Pulling text from the right of a string until you hit a space

[glennpc]

I have a string of text in a cell that begins with a DATE then a space, then a variable set of text data (which has spaces in it), followed by a space, and finally out on the right, a monetary value that doesn't have a dollar sign and could look like any of these: 14.00, 153.34, 1,234.15. There is nothing to the right of the monetary value.

I found a formula that pulls just the DATE from the left of the string. Assuming my string is in A1:
=LEFT(A1,FIND(" ",A1,1))

But is there a similar RIGHT formula that can give me the monetary value?

 

[eibi]

How about this option:

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Hat tip to:
http://exceltactics.com/reverse-string-search-excel-using-find/
for the concept.

Note that this returns the monetary value as a text string, not a numeric value.

 

[David Evans]

=TRIM(RIGHT(SUBSTITUTE(TRIM(A1), " ", REPT(" ", LEN(TRIM(A1)))), LEN(TRIM(A1)))) seemed to work on a quick and dirty example I cobbled together ...

Of course change "A1" to the cell address you're manipulating ...

 

[Mike H..]

I have a string of text in a cell that begins with a DATE then a space, then a variable set of text data (which has spaces in it), followed by a space, and finally out on the right, a monetary value that doesn't have a dollar sign and could look like any of these: 14.00, 153.34, 1,234.15. There is nothing to the right of the monetary value.

I found a formula that pulls just the DATE from the left of the string. Assuming my string is in A1:
=LEFT(A1,FIND(" ",A1,1))

But is there a similar RIGHT formula that can give me the monetary value?

Hi,

Try this

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",256)),256))

 

[eibi]

Mike,

I like this concept -- and I ran a test and found that not only is your solution simpler, it also runs considerably faster when applied to a large block of cells.

Is there a reason you've picked 256 or is that just a standard placeholder that you use?

 

[Mike H..]

Mike,

I like this concept -- and I ran a test and found that not only is your solution simpler, it also runs considerably faster when applied to a large block of cells.

Is there a reason you've picked 256 or is that just a standard placeholder that you use?

Hi,

The rationale for 256 is very simply it must be as long or longer than the length of the string you're trying to extract and 256 is a pretty safe bet so I guess you're correct it's a standard placeholder for me. You could use len(a1)+1 but that's an extra function call which I like to avoid.

 

[glennpc]

Thanks everybody for all your help and input on this. Mike's solution is PERFECT for my use-- I'm not a formula expert and that one is easier for a novice to figure out! You guys are geniuses. Thanks again.