Ask Your Question

How do I separate letters and numbers into separate columns? [closed]

asked 2016-12-07 17:24:05 +0100

Jodie gravatar image

updated 2016-12-07 18:10:51 +0100

karolus gravatar image

I have a list of device IDs starting with several letters and ending in several numbers, (see below) I would like to separate them so that I have the letters in one column and the numbers in the next column. Thanks for help in advance!


@Lupp: it seems the IDs are in one Column : edit by Karolus


edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-09-16 13:19:44.210783


Please clarify whether the "list" is text contained in a single cell or whether it is content of three adjacent cells in a row given here separated by spaces - or something else like each ID in its own row. (best case).
In addition a solution by formulae will be simpler if you can assure that the numeric part at the end is always 4 digits.

Lupp gravatar imageLupp ( 2016-12-07 18:04:15 +0100 )edit

each ID is in its own cell, in one column

Jodie gravatar imageJodie ( 2016-12-08 09:38:45 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2016-12-07 18:06:08 +0100

karolus gravatar image


1st step:
→search and replace: \d+ by '& with option regular Expression

2nd step:
→Data→Text in Columns… Delimiter [x]other ' choose Text instead Standard for second Column to pretend the leading zeros cutting off.

edit flag offensive delete link more


perfect thank you so much!

Jodie gravatar imageJodie ( 2016-12-08 09:47:37 +0100 )edit

Question Tools

1 follower


Asked: 2016-12-07 17:24:05 +0100

Seen: 1,283 times

Last updated: Dec 07 '16