Remove numbers from alphanumeric cells

**amdaln** · 09-17-2008, 04:59 PM

Hi.

I have about 10000 cells of varying length in text and numbers but they all end with numbers on the ends of them. Using text to columns doesn't work because they're all different lengths and it cuts them off in the wrong place.

Is there a formula I can use that will return only the text from these cells and remove the numbers. It would definitely save me time from individually deleting the numbers at the end.

Thanks so much!!

**VBA Noob** · 09-17-2008, 05:05 PM

Is there a pattern to the numbers at the end.

Maybe you can post a few sample's

VBA Noob

**amdaln** · 09-17-2008, 05:10 PM

EXAMPLE:

BARNEY & LARKIN CONSTRUCTION 1003
BARNEY & LARKIN CONSTRUCTION 1008
BARNEY & LARKIN CONSTRUCTION 1015
BARNEY & LARKIN CONSTRUCTION 993
BARNEY & LARKIN CONSTRUCTION 997
BRICES CATERING SERVICE 51072
BRICES CATERING SERVICE 60023
BRICES CATERING SERVICE 65237
BRICES CATERING SERVICE 78
C&D LANDSCAPE CO 15597
C&D LANDSCAPE CO 15825
CALIFORNIA BLACK CHAMBER COMM. APR/JUN-06
CANBY LANDSCAPE 1005
CANBY LANDSCAPE 1023
CANBY LANDSCAPE 1047
CANBY LANDSCAPE 1078
CANBY LANDSCAPE 154
CANBY LANDSCAPE 174
CANBY LANDSCAPE 182
CAP INDEX, INC 227090
CAPITAL SHEET METAL INC 32020
CAPITAL SHEET METAL INC 32021
CAPITAL SHEET METAL INC 32120
CAPITAL SHEET METAL INC 32121
CAPITAL SHEET METAL INC 32185
CAPITAL SHEET METAL INC 32326
CAPITAL SHEET METAL INC 32360
CAPITAL SHEET METAL INC 33113
CAPITAL SHEET METAL INC 34711
CAPITOL CITY DOOR INC 18177
CAPITOL CITY DOOR INC 59452
CAPITOL CITY DOOR INC R16361
CAPITOL CITY DOOR INC R16457
CAPITOL CITY DOOR INC T17647
CAPITOL CITY DOOR INC T18346
CAPITOL CITY DOOR INC T18446
CAPITOL CITY DOOR INC T18460
CAPITOL CITY DOOR INC T18496
CAPITOL CITY DOOR INC T18532
CAPITOL CITY DOOR INC T18620
CAPITOL CITY GLASS CO, INC 59452
CAPTURE TECHNOLOGIES INC. 30001524
CARPET ETC 45968
CARREKER CORPORATION 015509
CARREKER CORPORATION 13148
CARREKER CORPORATION 13320
CARREKER CORPORATION 13321
CARREKER CORPORATION 13588
CARREKER CORPORATION 14203
CARREKER CORPORATION 14663
CARREKER CORPORATION 14943
CARREKER CORPORATION 15093

See. It goes on and on and I only need the business name minus the invoice numbers. But It would take forever to individually edit these, even if I delete the numbers from one and drag down. I'd rather just remove the numbers quickly.

**VBA Noob** · 09-17-2008, 05:24 PM

Maybe

=LEFT(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1)

VBA Noob

**amdaln** · 09-17-2008, 05:43 PM

Genius!! Thanks so much. I wish I understood what it all meant. Would it be too much to ask what the breakdown is?

Amber

**VBA Noob** · 09-17-2008, 05:48 PM

Search excel help on the functions.
Link may help also

http://www.mvps.org/dmcritchie/excel/strings.htm

VBA Noob

**amdaln** · 09-17-2008, 05:52 PM

Thanks so much for the help.

**amdaln** · 09-18-2008, 02:12 PM

Okay, I have another question about this same thing.

Here are the cells originally --

APPROVED BY C SOULE WASHINGTON CO SHERIFF`S OFFICE 1827H
APPROVED BY C SOULE WASHINGTON COUNTY SHERIFF`S 1827H
APPROVED BY D BATES AMTECH LIGHTING SERVICES 183304857
APPROVED BY D BATES AMTECH LIGHTING SERVICES 183408756
APPROVED BY D BATES ANIXTER INC 135421278
APPROVED BY D BATES ANIXTER INC 135421337
APPROVED BY D BATES B&C TELEPHONE INC 106151
APPROVED BY D BATES B&C TELEPHONE INC 110069
APPROVED BY D BATES B&D ELECTRONIC SALES MOVED FROM CIP TO EXPENSE (CC)
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 25924
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35529
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35662
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35705
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35705
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35753
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 35765
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 36205
APPROVED BY D BATES B&D ELECTRONIC SALES, INC. 36341

I have the forumla to remove the numbers (thank you very much VBA Noob) Is there one I can add on to remove the first 4 separate words? Approved by Blank Blank?

It would be super helpful.

**ChemistB** · 09-18-2008, 02:40 PM

IF your strings always start with "Approved by ? " where ? represents the initial only of the first name (and there's never anyone with a middle initial), then this simple one will work

Please Login or Register  to view this content.

15 would work, I chose 16 to be on the safe side.

**amdaln** · 09-18-2008, 04:47 PM

The forumla you gave me removes the vendor name, not the APPROVED BY NAME.

I'm trying to change the cells to just be the Vendor name.

Thanks though!!

**.excelhelper.** · 08-19-2009, 01:12 PM

“=LEFT(A1,FIND("^^",SUBSTITUTE(" "&A1," ","^^",LEN(" "&A1)-LEN(SUBSTITUTE(" "&A1," ",""))))-1)”

I modified the above formula from Left to Right in order to drop the numbers off the front of some data but the result are not what I expected.

Data Example
123 Alpha Rd
11223 Alpha Trl
123 Bravo Rd
1124 Alpha Rd

Formula generated Values:
3 Alpha Rd
23 Alpha Trl
3 Bravo Rd
24 Alpha Rd

Any help would be greatly appreciated.

Thanks,

**DonkeyOte** · 08-19-2009, 01:15 PM

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.