Find string

[rpadminserver]

Hello All.

I have data in cells that look somthing like this -
SIG A Arm, AN 9.7

I trying to come up with a formula that find the last instance of "AN " (that is AN with a space)(and yes there could be another instance of "AN ", in the string, I only need to find the LAST instance) and give me remaining number.

In this example I would want 9.7 to appear in my cell.

Thanks all!

 

[Richard Schollar]

Hi

Try:

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

 

[Rick Rothstein]

...(and yes there could be another instance of "AN ", in the string, I only need to find the LAST instance) and give me remaining number.

Clarification please... will the last "AN " and the number that follows it always come at the end of the string like your example shows or could there be other text after it at the end of the string? If so, will there always be a space after the number for that case?

 

[rpadminserver]

PERFECT!

Thank you for the quick and correct response!

 

[hiker95]

rpadminserver,

Something like this?

Excel Workbook
	A	B
1	SIG A Arm, AN 9.7	9.7
Sheet1

 

[hiker95]

rpadminserver,

This may be better.

Excel Workbook
	A	B
1	SIG A Arm, AN 9.7	9.7
Sheet1

 

[rpadminserver]

Thank you all very much.

I have what I need

FYI....

Rick... my string will always end with AN ####, and there will never be another AN after that.

 

[Rick Rothstein]

rpadminserver,

This may be better.

Excel Workbook
	A	B
1	SIG A Arm, AN 9.7	9.7
Sheet1

That won't work if there is more than one "AN " in the string as the OP indicated there might be.

 

[Rick Rothstein]

Rick... my string will always end with AN ####, and there will never be another AN after that.

Actually, I was asking if "AN #.#" would always end your string. I wanted to make sure that you couldn't have something like this...

Text text AN 1.2 text text AN 9.7 more text at the end

If the number will always be at the end of the text, then you can use this formuls as well...

=LOOKUP(999,--RIGHT(A1,ROW(INDIRECT("1:99"))))

where the 999 must be a number larger than the largest number that can appear anywhere within the text. If you know that you will never insert any rows within the first 99 rows of your worksheet, the above formula can be made more efficient...

=LOOKUP(999,--RIGHT(A1,ROW($1:$99)))