Seperating numbers and text

how do they progress? first you can take the group and remove spaces, then using text to columns with delimited and comma as a separator you can put each in its own cell. But you note that you have about 5000 questions, are they like 10a, 100a, 1000a etc? or do they repeat with the numbers?

Originally Posted by Sambo kid

how do they progress? first you can take the group and remove spaces, then using text to columns with delimited and comma as a separator you can put each in its own cell. But you note that you have about 5000 questions, are they like 10a, 100a, 1000a etc? or do they repeat with the numbers?

The numbers repeat. For example:

"Old Questions: 1e, 2c, 3a, 4e, 5d, 6e, 7a, 8b, 9d, 10a. New Questions: 1d, 2a, 3f, 4e, 5c, 6a, 7c, 8c, 9b, 10a. Next Topic: 1c, 2v, 3c, 4d, 5d, 6d, 7a, 8c, 9a, 10a..."

It goes on and on like a sting of code. That's why I would like to separate it out so I can reference sections at a time without searching the whole line for the question that I am on.

what I would do is, if they are all in row 1, use find and replace, find " " (a space) and replace (leave blank) then hit replace. this will get rid of all the spaces (if there are any). then use the text to columns function and get them all into their own cells going across row 1. If there are 5000 or so of them you have over 16000 columns if you are using excel 2007 or later. Then highlight the row and hit copy, then move down a row and hit paste special transpose and they will now be vertical in your column. Then use this formula in B2 across from your first value... =LEFT(A2,(LEN(A2)-1)) and copy down. Then in column C (cell C2) this =RIGHT(A2,1) and copy down.

under the data tab in excel is a function called text to columns. You may not need it as I reread your first post. You said that those values were in one line. If they are in one cell then you need that function, if they are spread out over the columns across row 1 then you don't need it. So I'm not sure. Either way, once you separate the values (or if they are separated already) then highlight the row and use copy then move down to the next cell and paste >> special >> transpose and they will all be in one column. Then use those two formulas and your results will be in columns B and C.

EDIT: I was typing my post when you wrote it worked.

Glad I could help.
AND, thank you for the rep!