-1

I am coding an Excel sheet and need to split data from a cell that is formatted as a percent with a code in front of it (e.g. "RPD 4.4%").

I want this formatting to remain the same in the original cell but only want to pull in 4.4 to the desired cell.

What is the simplest equation that I could use to do this?

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
ODCODE
  • 31
  • 3
  • 2
    Will the data never contain more than 2 spaces? In that case you could use a combination of MID/FIND. Else you could look into FILTERXML (look at this explanation from JvdV https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml ) – P.b Dec 21 '21 at 16:52
  • FILTERXML() would be best choice. You can also use Left, Righ, Mid function with Substitute. – Harun24hr Dec 21 '21 at 17:40
  • What formulas have you tried? And what problems have occurred with them? – Ron Rosenfeld Dec 21 '21 at 19:56
  • @RonRosenfeld I really just started with parsing out RPD into a separate cell using =IF(B2="","","RPD") and was looking into the above suggestion of Left, Right functions but was getting stuck with errors. And yes, it will be in the same format with only two spaces in all scenarios. – ODCODE Dec 21 '21 at 20:15
  • @ODCODE Then you should be able to do it with using `LEFT`, `MID`, and `FIND` and probably `SUBSTITUTE` and `LEN` – Ron Rosenfeld Dec 21 '21 at 20:19
  • OK, I will look into that. the 4.4 value will be different each time. Spacing, and RDP % will remain the same. I will circle back if I run into problems. Thanks to all the suggestions! – ODCODE Dec 21 '21 at 20:20
  • 1
    @ODCODE if that's the case, then you really only need a formula for the numeric value. Word1 = `RDP` all the time, Word2 = `=--SUBSTITUTE(SUBSTITUTE(B2,"RPD",""),"%","")` – Ron Rosenfeld Dec 21 '21 at 20:33
  • @RonRosenfeld Thank you this worked out well! – ODCODE Dec 22 '21 at 17:14

1 Answers1

-1

Screenshot(s)/here refer.

*EDITED: INCLUDED METHOD 4 (MOST ROBUST: SEPARATES DIGITS FROM NON-DIGITS)

Method 1:

=TRANSPOSE(FILTERXML("<x><y>"&SUBSTITUTE(Table1[@Data]," ","</y><y>")&"</y></x>","//y"))

Method 1 - FilterXML

Method 2

Method 2 - part 1 (mid)

=MID(Table1[Data],1,SEARCH(" ",Table1[Data])-1)

Method 2 - part 2 (substitute)

=SUBSTITUTE(Table1[Data],G5#&" ","")

Method 3

Method 3 - part 1 (mid)

=MID(Table1[Data],1,SEARCH(" ",Table1[Data])-1)

Method 3 - part 2 (mid)

=MID(Table1[Data],SEARCH(" ",Table1[Data])+1,LEN(Table1[Data]))

EDITED Method 4

Method 4 - part 1: all non-digits/decimals

=LET(r_1,IFERROR(FILTERXML("<x><y>"&MID(B5,SEQUENCE(1,LEN(B5),1,1),1)&"</y></x>","//y"),""),r_2,1-(--ISNUMBER(r_1)+(r_1=".")),TEXTJOIN("",1,IF(r_2=1,r_1,"")))

Method 4 - part 2: all digits/decimals 

=LET(r_1,IFERROR(FILTERXML("<x><y>"&MID(B5,SEQUENCE(1,LEN(B5),1,1),1)&"</y></x>","//y"),""),r_2,--ISNUMBER(r_1)+(r_1="."),TEXTJOIN("",1,IF(r_2=1,r_1,"")))

Pros/Cons

Method Pro Con
1 • row-wise array • Office 365 req. • Implementation: no 'column-wise' arrays • Fails for non 'space-delimited' data
2 • column-wise array • intuitive/auditable • Fails for non-"RPD" prefix • Fails for non 'space-delimited' data
3 • as for 2 + • robust (can work with non-"RPD") • Fails for non 'space-delimited' data
4 • robust (separates digits from non-digits) •decimal friendly • Office 365 req. • complex • not row- /column- 'wise' arrays
JB-007
  • 2,156
  • 1
  • 6
  • 22