CODEWITHSUNDEEP
excelsplitformula
ReddaJoppe
ReddaJoppe

Reputation: 528

MS Excel: Return all characters before first number

I MS Excel 2013 I have a column that lists postal code information. It contains a 4 digit postal code followed by a city name, but in some fields the postal code is preceeded by a town name, like this:

7100 Vejle

8600 Silkeborg

Grauballe 8600 Silkeborg

I want to split this data into three columns: town name (empty in most instances), postal code and city name, so I need to a formula that returns all characters before the first number, but I can't figure out how to do that.

Upvotes: 0

Views: 64

Answers (1)

mike7mike
mike7mike

Reputation: 433

I would use "Text to Columns", using Space as the delimiter. This will split your data into two or three columns depending on whether there is a town name present or not. You can then use a formula on this data to sort the data into correct columns.

Assuming that once you have done Text to Columns your data is present in Columns B, C and sometimes D, entering this formula adjacent to your data, say in F2, and dragging across three columns, and downward for all your rows, will sort your data correctly:

=IF($D2="", IF(A2="", "", A2), B2)

Upvotes: 1

Related Questions