MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Text to Columns..


Posted by Mark on January 04, 2002 11:26 AM


I'm also trying to find a way to automate the removal of numbers from a numbered list. So, if I have a list that looks like the following:

1. lkjhfdlhf
2. ljfdljhs
3. ouyreoiure
4. ;rsdliuds

I want to be able to strip away the "1. " and JUST leave the text. Keep in mind, many of these numbered lists go past 10, so I can't just have it chop a certain number of characters off by default. Any ideas?


Posted by Troy on January 04, 2002 11:31 AM

If all the numbers are followed with a "." as the sample data is, you could use the find() function and then keep everything past that point. I'm also trying to find a way to automate the removal of numbers from a numbered list. So, if I have a list that looks like the following: 2. ljfdljhs 3. ouyreoiure 4. ;rsdliuds

Posted by Mark on January 04, 2002 11:35 AM

I've thought of that, but unfortunately the instances of "." are often followed by spaces (sometimes multiple spaces) after them. I want to get rid of absolutely everything before the first character in each line.. : : I'm also trying to find a way to automate the removal of numbers from a numbered list. So, if I have a list that looks like the following: : 2. ljfdljhs : 3. ouyreoiure : 4. ;rsdliuds

Posted by John on January 04, 2002 2:25 PM

This should work for lowercase letters - enter as an array function:

=MID(A1,MIN(IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>96,ROW(INDIRECT("1:"&LEN(A1))),100)+IF(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<123,ROW(INDIRECT("1:"&LEN(A1))),100))/2,100)

I'm also trying to find a way to automate the removal of numbers from a numbered list. So, if I have a list that looks like the following: 2. ljfdljhs 3. ouyreoiure 4. ;rsdliuds