text to column delimiter split at any number

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

Preferrably simpler than this.

2 Answers

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!):

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.

Have a look at this article from microsoft: