Formula to extract number from a string

**Bobbbo** · 03-26-2017, 02:22 PM

I am looking for a formula that can find a specific word then display the value associated with that word.

Attached is a speard sheet that has a Cell B4 that has a string of data that I would like to extract the number value associated with the words "Ratio1", "Ratio2"... and on then find the number that is to the right of that word "Ratio1", "Ratio2"... and show the value.

This is cell B4

Please Login or Register  to view this content.

And I want a formula that will result in the number to the right of the words Ratio1, Ratio2...

The desired results are in cells D4:D9

D4 would have this

Please Login or Register  to view this content.

D5 would have this:

Please Login or Register  to view this content.

The word "Ratio1" can be of any length and can be different the length of "Ratio2". Also the number can be of any length.

I hope this one is simple!

**JohnTopley** · 03-26-2017, 03:07 PM

Try

in C4

=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4,"""",""),":","="),"}",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4,"""",""),":","="),"}",""))-14)

Assumes [ABC_CacheA] { is "constant"

in D4

=TRIM(MID(SUBSTITUTE($C$4,",",REPT(" ",99)),(ROWS($1:1)-1)*99+1,99))

Copy down

**Bobbbo** · 03-26-2017, 03:30 PM

Originally Posted by JohnTopley

Try

in C4

=RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4,"""",""),":","="),"}",""),LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B4,"""",""),":","="),"}",""))-14)

Assumes [ABC_CacheA] { is "constant"

in D4

=TRIM(MID(SUBSTITUTE($C$4,",",REPT(" ",99)),(ROWS($1:1)-1)*99+1,99))

Copy down

Thanks John, It works great. The only issues is my actual data string has 11 Ratios "Ratio1" through "Ratio11" How would I change your formula for 11 Ratios rather than the 5 I showed in my example?

**JohnTopley** · 03-27-2017, 04:28 AM

Try

in D4

=TRIM(MID(SUBSTITUTE($C$4,",",REPT(" ",250)),(ROWS($1:1)-1)*250+1,250))

Copy down

**Bobbbo** · 03-27-2017, 06:23 PM

Originally Posted by JohnTopley

Try

in D4

=TRIM(MID(SUBSTITUTE($C$4,",",REPT(" ",250)),(ROWS($1:1)-1)*250+1,250))

Copy down

Thanks John, I got it to work but needed to change the 250 number to 500 and 1,250 to 1,500.!

**JohnTopley** · 03-28-2017, 03:04 AM

See attached.

=TRIM(MID(SUBSTITUTE($C$4,",",REPT(" ",300)),(ROWS($1:1)-1)*300+1,300))

Formula to extract number from a string

LinkBack

Thread Tools

Rate This Thread

Display

Formula to extract number from a string

Re: Formula to extract number from a string

Re: Formula to extract number from a string

Re: Formula to extract number from a string

Re: Formula to extract number from a string

Re: Formula to extract number from a string

Thread Information

Users Browsing this Thread

Similar Threads

Extract number from string

Need Formula to extract number from text string

Mid and Mod to extract a number from string

[SOLVED] How to Extract A Number from String if Text within the String Equals XYZ

[SOLVED] Extract a mainly numeric string (ISSN number) from an arbitrary string

Extract Number from String Help

Extract number out of a string

Bookmarks

Bookmarks

Posting Permissions