Selecting numeric digits embedded in a cell in Excel 2

[JC10001]

For the life of me, I can't figure out a way to do this.

But, for example, I have a bunch of cells that look like this:

New Jersey Investment Total Value: $873,903,301.03Total Employees: 3801

New Hampshire Investment Total Value: $40,781,203.16Total Employees: 183

etc.

So each state has its own cell. I need to come up with a macro that will extract the numeric digits (including the decimal) only. For example, the long variable, StateValue should equal 873803301.03 for New Jersey.

Any help or advice will be very much appreciated

 

[JC10001]

I made a slight mistake in my above text. There is no $ sign in the cells.

Anyway, I think somehow getting a macro to look for "Value: " and then store the numbers until the next "Total" would be a good start.

If anyone has any suggestions, I would definitely appreciate it.

 

[sfvb]

Here's a formula that will work provided the formatting is the same across all cells:

=VALUE(MID(A1,FIND(":",A1)+2,(FIND("Total Emp",A1)+1)-(FIND(":",A1)+3)))

 

[DaleWatson123321]

JC,

Here's an assist for sfvb. His formula works well for the Total Value; however he didn't include a formula for the Total Employees.

This formula will work for the Total Employees...
=VALUE(MID(A1,FIND("Total Emp",$A1)+17,999))

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[JC10001]

sfvb and Dale Watson - you guys are great! Thank you so much! I really appreciate the help.