Remove Numbers From Alphanumeric Cell

[davehunter]

In cell A1 i have the value ABC123.

I want to remove the 123.

Can you tell me how to do this?

Thanks!
Dave

[VBA Noob]

Link may help

http://www.excelforum.com/showthread.php?t=619035

VBA Noob

[davehunter]

no, i'm looking to delete the 123 and just leave abc

[VBA Noob]

Try this macro. Select cells and run

Please Login or Register  to view this content.

VBA Noob

[davehunter]

Takes hours to run that mate. I'm working on 60,000 rows or so.

[dominicb]

Good morning davehunter

Takes hours to run that mate

Well, you could always do it manually.

You have to remember that just because Microsoft have given you 64,000 rows you don't have to use all of them. If you have 60,000 rows, X number of columns and Y number of sheets then maybe Access is the right tool for the job.

Any type of macro is going to take a while to affect 60,000 rows in Excel 'cos of the way data is stored. It's a number cruncher - not a database.

DominicB

[starguy]

Takes hours to run that mate. I'm working on 60,000 rows or so.

Select the range.
Press Ctrl+H
Find What: 1
Replace With: <leave it blank>
Press Replace All

Then change
Find what: 2 and press Replace All
and so on for all numbers (i-e 3, 4, 5, 6, 7, 8 and 9).

It will take not more than 20 seconds.

[davehunter]

I have a cell A1 with value ABC123.

I have been provided with a function that will extract the numbers from this cell, and enter them as required.

Please Login or Register  to view this content.

In cell B1 if I enter =RemoveTexts(A1) the value 123 is left. Spot on.

I am now trying to do the opposite.

In C1 I want ABC to appear. Would IsText or something similar allow me to do this with the above function.

Thanks in advance.

Dave

[mrice]

Try

Please Login or Register  to view this content.

as this is a Boolean function.

[davehunter]

Try

Please Login or Register  to view this content.

as this is a Boolean function.

Just returns 0

[VBA Noob]

Please remember to follow the rules below by avoiding duplicate posts (Still not resolved this one) and post to the correct group

see if this mod works for you

Note will also be slow if used on 60000 rows

Please Login or Register  to view this content.

VBA Noob

[davehunter]

Please remember to follow the rules below by avoiding duplicate posts (Still not resolved this one) and post to the correct group

see if this mod works for you

Note will also be slow if used on 60000 rows

Please Login or Register  to view this content.

VBA Noob

Thats brilliant thanks!

Dave

[davehunter]

This function can be used to extract numbers from an alphanumeric cell:

Please Login or Register  to view this content.

[VBA Noob]

Think you mean this does

Please Login or Register  to view this content.

VBA Noob

[davehunter]

This will extract numbers i.e. keep them:

Please Login or Register  to view this content.

This will remove numbers i.e. keep text:

Please Login or Register  to view this content.

sorted

[Julie Roxburgh]

Perfect, thank you so much!