Extracting Multiple Number Values from Text cells into separate cells

[skydoc]

I am interested in

These are the typical text cell values I have in lets say column A.

Interval

144 MO OR 12000 FC

100 MO OR 10000 FC OR 15000 FH

14800 FC OR 29700 FH

10200 FC OR 20500 FH

12 YE OR 13300 FC OR 57100 FH

Looking for a way to extract the numeric values from the text and place them into columns B, C, D while also converting calendar values to (i.e. YR=year, MO=month, DY=days in to a day value like this:

Days	FH	FC
4,380		12,000
3,041	15,000	10,000
	29,700	14,800
	20,500	10,200
4,380	57,100	13,300

Thanks for advance for any assistance or clues you can offer.

[JohnTopley]

Try

In B

=IFERROR(INT((LEFT($A1,FIND("MO",$A1)-1)+0)/12*365),"")


In C

=IFERROR(MID($A2,FIND("FH",$A2)-6,5)+0,"")

in D

=IFERROR(MID($A1,FIND("FC",$A1)-6,5)+0,"")

[skydoc]

Hi JohnTopley,
The formula provided is a step in the right direction. I have applied it an have come across these issues.

With respect to your formula in Column B
The formula works - I will have to apply some form of ELSE / IF formula to capture the requirements if the value is expressed in YR or DY instead of MO.

With respect to your formula in Column C
This formula captures the requirement perfectly if the FH numeric value FC is 5 digits. There are times when I have FH values spanning 1 to 6 digits long (i.e.,. 7 FH or 105000 FH).

With respect to your formula in Column D
This formula captures the requirement perfectly if the FC numeric value FC is 5 digits. There are times when I have FC values spanning 1 to 6 digits long (i.e., 1 FC or 100000 FC).

[JohnTopley]

Try ..


data starting row 2

in Column B

=SUBSTITUTE(SUBSTITUTE($A2,CHAR(10),"/"),"/OR",""

in C

=IFERROR(INT((LEFT($B2,FIND("YR",$B2)-1)+0)*365),IFERROR(INT((LEFT($B2,FIND("MO",$B2)-1)+0)/12*365),LEFT($B2,FIND("DY",B2)-1)+0))

in D (FC)

=IFERROR(MID($B2,FIND("/",$B2)+1,FIND("FC",$B2)-FIND("/",$B2)-2),"")

in E (FH)

=IFERROR(MID($B2,FIND("FC/",$B2)+3,FIND("FH",$B2)-FIND("FC/",$B2)-2),"")

[skydoc]

Thanks JohnTopley

Nice approach!

The formula on the spreadsheet you attached has a different formula in cell D2 vs. D3 and D4 - also noticed a different formula in between E2 vs. E3 and E4. Is this by design and the formula can repeats from row 3 onwards? This is highlighted in yellow cells on the attached spreadsheet.

The formula in Column B seems to work well with all combinations of data.

The formula in Column C, D, E work in the scenarios presented by myself. The current issue is that IF the values in Column A are not in the same YR FH FC sequence (i.e, 100 FC OR 100 FH vs. 100 FH OR 100 FC) the the formulas result present a #VALUE. I noticed some other issues when Column A is a single FC or FH value (i.e, 100 FH).

I have added some examples of where it does not work and highlighted in RED font where an answer is expected. I will change the items in ORANGE font to show BLANK because there is no result to be expected.

Perhaps an array can be incorporated into your formula as described in https://exceljet.net/formula/split-t...d-numbers?????

[JohnTopley]

I resorted to VBA:

Firewall will not let me post the code!!!

[Phuocam]

See this ...

[JohnTopley]

Formula in B

=IFERROR(LOOKUP(10^10,--MID($A2,SEARCH("DY",$A2)-ROW($1:$10),ROW($1:$10))),IFERROR(INT(LOOKUP(10^10,--MID($A2,SEARCH("MO",$A2)-ROW($1:$10),ROW($1:$10)))/12*365),IFERROR(LOOKUP(10^10,--MID($A2,SEARCH("YR",$A2)-ROW($1:$10),ROW($1:$10)))*365,"")))

[skydoc]

It works great!!!! You are Brilliant!!!!

I am studying the formula to try to understand the use of ^ and ROW ($1:$10).

Thank you so very much!

[skydoc]

Thanks Phuocam,

You are Awesome!!!

I am studying both of your formulas to understand the use of 10^10 and ROW application.

Thanks a bunch!

[Phuocam]

You're welcome!