Removing unwanted characters after a number

[RaydenUK]

I have a list of data arranged in column A that looks like this:

Blank
Neg2
Tom_Tom
Bob_Bob4
123566_A_B
135325_S_G_F
John
13543556_D
13254322
16513545_B_D_S_R_S
Bill_Bill
Larry

I have been trying to figure out a code that will remove anything after the numbers in the list, so 123566_A_B would become 123566, along with all the other rows of data that starts with numbers. The other rows that start with

[stnkynts]

Are all the cells, in which you want to remove the characters, seperated by an underscore?

[RaydenUK]

Yes, always.

[RaydenUK]

it looks like I didn't finish my original post, but I meant to say that anything else in the list with an underscore does not need to be removed, only the rows that start with a number.

[stnkynts]

Based upon the parameters you have supplied this should do as you have asked

Please Login or Register  to view this content.

[MarvinP]

Hi Rayden,

Here is my code to do the task also. I have a User Defined Function that looks for a number in the first character and then trims at the underscore character.
I think stnkynts code will work also but he uses For Each and Split that is harder than normal string functions.

Please Login or Register  to view this content.

[RaydenUK]

Wow that works perfectly! thank you! Now is there any way to have this to loop through all the pages in my workbook, with the exception of "Master" and "Plate"?

[MarvinP]

Hey Rayden,

Here is a Sub that will loop through all sheets an do the above.

Please Login or Register  to view this content.

[stnkynts]

My Code:

Please Login or Register  to view this content.

Marvin's Code:

Please Login or Register  to view this content.

That way you have options

[RaydenUK]

Wow you guys are amazing! You have exceeded my expectations! Thank you.