# Extracting Numeric / Alpha-Numeric data from Cells using VBA

#### danburgan

##### New Member
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

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### AlKey

##### Active Member
Assuming string is in A1

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

#### AlKey

##### Active Member
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

##### New Member
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

##### MrExcel MVP
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

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

Thanks again.

#### shimaa01234

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

Last edited:

Replies
15
Views
472
Replies
12
Views
848
Replies
1
Views
535
Replies
10
Views
646
Replies
0
Views
294

1,191,116
Messages
5,984,735
Members
439,905
Latest member
VBAhelpplz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back