Extract words and numbers from comples string (Excel 2010)

[FredStimler]

I converted a PDF file with important TAX rate information. It did not work very well so now I must grab values from the string into 3 different columns. An example string is "Agua Dulce 9.00% Los Angeles".

The first word (City) is from position 1 to where there are two blank spaces.
The rate is the first numeric on the page - grab the decimal and 2 more places
The last word (County) is the next word - to the end of the string.

I have used Find, Substitute, IsNumber, Search, MID and others but am confused on how to combine them here.
Thanks.

 

[Ron Coderre]

With
A1: containing the text to parse, in the format: text(containing spaces) followed by a number_in_percent_format followed by other text
Example: Agua Dulce 9.00% Los Angeles

These formulas return the
city

B1: =TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

Rate

C1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))

County

D1: =TRIM(MID(A1,SEARCH("%",A1)+1,LEN(A1)))

In that example, the formulas return:
Agua Dulce
0.09
Los Angeles

Is that something you can work with?

 

[FredStimler]

Thank you Ron. The only issue I still have is that often the space for CITY is 20 characters before the Rate starts. You RATE and COUNTY formulas were perfect. How can I restrain the CITY so it stops at the first digit? Right now the City formula returns "Anaheim* 8.00% Orange" from the source cell of "Anaheim* 8.00% Orange". Thanks.

 

[Ron Coderre]

I think the website replaced whatever is between "Anaheim" and "8" with an asterisk.
Can you find a way to put those values in or explain what goes there?

 

[FredStimler]

"Anaheim* 8.00% Orange"

In case it loses the spaces, there are 15 (I see between 10 and 15 on a normal basis.

 

[Ron Coderre]

For

A1: Anaheim                8.00% Orange

This formula returns: Anaheim

B1: =TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))

If you're seeing something else, then there's an issue with the data.

 

[Sektor]

Here's UDF which would get every part you need. The second argument (iPart) is the part number you need: 0 - city, 1 - rate, 2 - county. Just put the number you need.

Function GetPart$(s$, iPart%)
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True: .Pattern = "(.+?)(\d+(?:\.\d+)?%)(.+)": GetPart = .Execute(s)(0).SubMatches(iPart)
    End With
End Function