Find a specific account's value from a range of text strings.

Need formula to find a specific account no. from a range of text and return that account's value as a usable numeric.

Account number:012345678 XZ
Account value as of 12/31/2013:$12,345,56

Account number:987654321
Account value as of 12/31/2013:$876,543.21

Thanks in advance for a response.

You mean like this??

Please Login or Register  to view this content.

assuming your data are in A1

Hi Glenn,
I will try to be more clear on what I am trying to do.
I have a range of text strings that contain account numbers and account values:

A
1 Account number:012345678 XZ
2 Account value as of 12/31/2013:$12,345,56
3
4 Account number:987654321
5 Account value as of 12/31/2013:$876,543.21
6
.
.
n

Find acct. "012345678 XZ" from range A1:A255 and display its value (12,345.56).
Insert an acct. no. into a generic formula to display the value in that acct. in a table.
The data for each account may be 3 to 6 rows each and there maybe 5 to 10 blank rows between each account.
Also there may be a CHAR 160 after the account number or the value figure that has to be stripped, (see row 4).
Thanks for working on this.

Yes, as AlKey said, please upload a sample sheet...

Hello Al
I haven't been able to find out how to upload a spreadsheet. Looking for your help.
Thanks.

The Spreadsheet did not copy much at all. I had a column of data of about 50 rows interspersed with blank rows, formulas in 2 other columns, and then a table
with a 3rd set of formulas. I will have to learn the proper upload labyrinth.
I will try to send again. Thanks for your patience.

Upload a Spreadsheet to ExcelForum 010614.xlsxHere's another try at uploading the speadsheet.

[Solved]:
The basic formula and syntax I needed to start a solution I found at http://www.excelbanter.com/showthread.php?t=123146, written by vezerid.
Where he wrote to a query that I recognized as fitting my own situation:
"If column B contains the value you want to retrieve and A contains the store codes, how about" :
=INDEX(B1:B100,MATCH(code,A1:A100,0)+1) Simple enough!
I probably missed or didn't recognize it a number of times when I searched the internet, that this basic formula would do what I wanted with the proper modifications. As I needed to extract numeric values from mixed values in variable strings of text, I pieced together this formula:

Final formula: '=VALUE(MID(INDEX($B$20:$B$150,MATCH("*"&N16&"*",$B$20:$B$150,0)+5),
SEARCH("$",INDEX($B$20:$B$150,MATCH("*"&N16&"*",$B$20:$B$150,0)+5))+1,12))
I don't know how efficient it is, but works great in my situation.
Many thanks to all responders.