Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

up vote 2 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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.