Ask Your Question
0

How do I separate letters and numbers into separate columns?

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!

FAPSCC2013
CPDF1099
FAPSC1482
CPFQ0999

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

Thanks!

edit retag flag offensive close merge delete

Comments

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
1

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

karolus gravatar image

Hallo

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

Comments

perfect thank you so much!

Jodie gravatar imageJodie ( 2016-12-08 09:47:37 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 727 times

Last updated: Dec 07 '16