Extracting Numeric / Alpha-Numeric data from Cells using VBA

[danburgan]

Good Afternoon All,

I am trying to automate a task to take data from one column and copy a district identifier to another. The data and the expected outcome are below.

District	Output
Congressional District 1	1
Congressional District 2	2
Congressional District 3	3
Congressional District 4	4
Congressional District 5	5
State Senate District 21	21
District 21	21
District 30A	30A
District 30B	30B
District 31A	31A
5th Judicial Circuit	5
5th Judicial Circuit	5
Legislative District 21	21

<colgroup><col><col></colgroup><tbody>
</tbody>

I can easily pull data from the first few rows using Mid, but I have three different scenarios to look at and the amount of data in the sheet is variable.

Any assistance with this is greatly appreciated.

Dan

 

[AlKey]

Assuming string is in A1

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

 

[AlKey]

Since I just noticed that there are some strings with numbers at the beginning, I think this one will work better.

=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)),FIND(" ",A1))

 

[danburgan]

AlKey,

So close and thank you for the assistance.

When I add the formula and view results we catch 2 out of 3 of the scenarios.

District	Output
Congressional District 1	1
Congressional District 2	2
Congressional District 3	3
Congressional District 4	4
Congressional District 5	5
State Senate District 21	21
District 21	21
District 30A	30A
District 30B	30B
District 31A	31A
5th Judicial Circuit	5th
5th Judicial Circuit	5th
Legislative District 21	21

<tbody>
</tbody>

The last is removing the "th" or "st" from the number values. These would show up as lowercase always.

 

[Rick Rothstein]

Assuming your numbers are in either the first or last "word" (as all your examples show), then this formula should do what you want...

=IF(ISNUMBER(-LEFT(A2)),""&-LOOKUP(0,-LEFT(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))))),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99)))

 

[danburgan]

Rick and AlKey. Thank you both. I was making this much more difficult than what it needed to be.

Thanks again.

 

[shimaa01234]

by the way
Mister, "Alkey" this guy a genius, in Excel forum