macro for remove number or letter from cell

[dancmin]

I am looking for 2 different Macros

I have a list of 100 (A1:A10000) names followed by numbers in the same cell. I would like to create a macro that will remove the numbers but will leave the name alone and like wise, remove name but leave numbers. For example:

John123 will be changed to John with the remove number macro

12Pete will be changed to Pete with the remove number macro

1D2a3n will be changed to Dan with the remove number macro

AND


John123 will be changed to 123 with the remove Letter macro


12Pete will be changed to 12 with the remove letter macro

1D2a3n will be changed to 123 with the remove letter macro

[JBeaucaire]

These two UDFs will do what you want. You can use them as cell formulas, or call them from within other VBA.

Please Login or Register  to view this content.

Used in a cell as:

=NumOnly(A1)
=TextOnly(A1)

[JBeaucaire]

Here's an example of how you can use the UDFs within another macro. With this macro, you select any range you want (column, row, or specific range) and activate the SelectChange macro and it will ask you what you want to do:

Please Login or Register  to view this content.

[dancmin]

oh wow thank you so much!!!

[dancmin]

jbeauclaire,
I cannot call the function i have to input

=Personal.xlsb!textonly(a1)

is there a way i can do JUST =textony(a1)?