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.

[TABLE="width: 272"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]District[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Congressional District 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Congressional District 2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Congressional District 3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Congressional District 4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Congressional District 5[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]State Senate District 21[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]District 21[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD]District 30A[/TD]
[TD]30A[/TD]
[/TR]
[TR]
[TD]District 30B[/TD]
[TD]30B[/TD]
[/TR]
[TR]
[TD]District 31A[/TD]
[TD]31A[/TD]
[/TR]
[TR]
[TD]5th Judicial Circuit [/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]5th Judicial Circuit [/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Legislative District 21 [/TD]
[TD="align: right"]21[/TD]
[/TR]
</tbody>[/TABLE]


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.

[TABLE="width: 272"]
<tbody>[TR]
[TD]District[/TD]
[TD]Output[/TD]
[/TR]
[TR]
[TD]Congressional District 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Congressional District 2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Congressional District 3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Congressional District 4[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Congressional District 5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]State Senate District 21[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]District 21[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]District 30A[/TD]
[TD]30A[/TD]
[/TR]
[TR]
[TD]District 30B[/TD]
[TD]30B[/TD]
[/TR]
[TR]
[TD]District 31A[/TD]
[TD]31A[/TD]
[/TR]
[TR]
[TD]5th Judicial Circuit[/TD]
[TD]5th[/TD]
[/TR]
[TR]
[TD]5th Judicial Circuit[/TD]
[TD]5th[/TD]
[/TR]
[TR]
[TD]Legislative District 21[/TD]
[TD]21[/TD]
[/TR]
</tbody>[/TABLE]


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