Selective text to column?

[Derekreation]

Hi everyone, I'm new the forums, and very new to using Excel VBE. I'm not too sure about its limitations and I may ask stupid questions, please be patient with me haha. Also if I'm being unclear on anything just let me know and I'll clarify. Thanks in advance for your help!

So let's simplify and say my worksheet just has 1 column and 50,000 rows. All the cells are filled with something like "000001-ABC_DEF_BLAH All_NEW HELLO WORLD_15x15", the main thing I have to do with A1 through A50,000 is to use text to column to get rid of the useless first number, in that example it should end up returning "000001" in A1, and "ABC_DEF_BLAH All_NEW HELLO WORLD_15x15" in B1. Now, if all the cells were in that format, it would be simple to use text to column with delimiter "-" to do so for all rows, but it's never that simple is it

The problem is that some of the cells don't have the useless number that I'm trying to get rid of. They are filled with values like "ABC_DEF_BLAH All_NEW-HELLO WORLD_15x15" where the "-" is in the middle of the string of text. I know for certain that the beginning number is always 5 or 6 numbers long, and IF the cell doesn't carry the useless number, and has a "-" in the middle, the "-" is never in the first 7 characters. Is there a way to use a macro to text to column selectively JUST if there is a "-" in the first 7 characters?

Thanks again and if I need to clarify anything please let me know!

-Derek

[FDibbins]

have you tried to use a mid() formula? if you can identify which 1st part you want to discard...or which middle part you want to keep, we could put something together that may help?

a sample file with either some representative examples...or some actual data if its not sensitive...would help us to help you better

[Richard Buttrey]

Hi, and welcome to the forum.

Rather than text to columns you could use two functions - there's probably a smaller more elegant one around the corner but it's getting late here...

B1:
=IF(ISERROR(FIND("-",LEFT(A1,7))),"",LEFT(A1,FIND("-",LEFT(A1,7))-1))
C1:
=IF(ISERROR(FIND("-",LEFT(A1,7))),A1,RIGHT(A1,LEN(A1)-FIND("-",LEFT(A1,7))))

[Derekreation]

Thanks for the prompt replies guys, and Richard that worked pretty much perfectly, really appreciate the help guys!

So unfortunately I realized I was wrong and that not all the useless numbers are followed by "-", some of them aren't followed by any symbols before going into the ABC parts, and some other useless numbers are followed by "_" or some other symbols. Richard's functions takes care of 98% of the 50,000 rows, which is awesome, but is there a way to take care of all the useless numbers in one stroke?

I guess a cleaner way to think about it is if the data in the cell begins with a number, get rid of that number AND any symbols immediately after the number and leave anything else. If the data in the cell begins with a useless number and is followed immediately by letters, then only get rid of the number. If it doesn't begin with a number we don't need to touch anything. Thanks again and sorry about the mistake!

-Derek

[Richard Buttrey]

Hello Derek,

If the alpha characters are all capital letters, how about

B1:
=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

C1:
=RIGHT(A1,1+LEN(A1)-MATCH(1,(CODE(MID(A1,ROW($A$1:$A$255),1))<=90)*(CODE(MID(A1,ROW($A$1:$A$255),1))>=65),FALSE))

Both of these are array formula and should be entered with Ctrl-Shift-Enter