Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I split a clustered alphanumeric column at the first instance of a number?

Preferrably simpler than this.

enter image description here

share|improve this question
    
You are importing from a CSV file, it seems. Maybe you can pre-process the information before loading it into the Excel spreadsheet? – Sudipta Chatterjee Feb 13 '13 at 17:46
    
I was just about to create the formula when I noticed the link you provided. I don't think you can do it much simpler than this! – Peter Albert Feb 13 '13 at 21:21
    
It's not from a csv. Thanks for your effort guys, I will see what I can do. – andrewk Feb 13 '13 at 21:30
    
Short of a Vba solution those giant formulas are the way to go for this. – Pynner Feb 14 '13 at 14:20
up vote 3 down vote accepted

To achieve what you want simpler than suggested by MS, do the following:

  1. Open your file using MS Word, not Excel.
  2. Open Replace dialog via CTRL+H.
  3. Set up the options as on the screen (Use wildcards MUST be checked!):

Wildcards Replace

These options (wildcards) mean the following:

  • Find what: ([0-9]{1,}) - means any digit 1 or more times. () brackets are required for future use - they mark string blocks.
  • Replace with: ,\1 - comma and 1st Find what block in () brackets.

Press Replace All - your strings will be converted like this: NY City 4a --> NY City ,4a

Now save the resulting file as TEXT (not Word Document!), change extension to CSV and simply open it in Excel. If your default CSV separator is comma - you'll have strings with comma separated into 2 cells. If you have different default separator - replace comma in the above "Replace with:" expression by this character.

Read more about wildcards: Finding and replacing characters using wildcards.

share|improve this answer
    
+1 I like this ingenious way – nixda Feb 15 '13 at 17:03
    
@nixda Frankly, for me Excel-based solution is much simpler, but if OP insists, so....)))) – Peter L. Feb 15 '13 at 17:06
    
Clever. It does get the job done. – andrewk Feb 15 '13 at 18:46
    
@andrewk Glad to help buddy) If you're happy with the solution proposed - please accept the answer by ticking it, this will mark the question solved. For the details see: Accepting Answers: How does it work? – Peter L. Feb 15 '13 at 18:50
    
@PeterL. why not – andrewk Feb 15 '13 at 18:54

Have a look at this article from microsoft: http://office.microsoft.com/en-us/excel-help/extracting-numbers-from-alphanumeric-strings-HA001154901.aspx

share|improve this answer
    
Well, have you noticed the link in the question? – nixda Feb 13 '13 at 20:35
    
yes -- sorry! Skipped over it at first. – F106dart Feb 14 '13 at 12:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .