Excel question

Mr_Wistles

Distinguished Member
Joined
Mar 11, 2004
Messages
11,938
Reaction score
2,493
Points
2,425
Location
Essex
Morning dudes and duddetes. I have an Excel problem that has got me stumped.

I have a list of cells which contain names (forename and surname) and a code number. I need to split this into three separate cells but I am unsure how to do it as the names are different lengths. Normally I would just use text to columns but the source data does not have a delimiter in it.
 
How can there be no delimeter? How do you know where the forename ends and the surname begins?

Example cell?
 
James Smith 12345678910
Abe Christianson 549753120

A dreadful extract but it apparently the best I can be given.
 
How very simple. I salute you Sir. Did not think you could use a space.
 
I have been trying all combinations of LEFT, RIGHT, BLANK etc. for the past 30 mins. In the end all it took was five mouse clicks!
 
Watch out for any exceptions (e.g. Arthur Conan Doyle). If there are any, you should be able to pick them up and correct manually.
 
Yep you can use any delimeter you like (would have answered earlier but had to get a cuppa :))
 
Watch out for any exceptions (e.g. Arthur Conan Doyle). If there are any, you should be able to pick them up and correct manually.

Some of them did crop up but it automatically added an extra column.
 
Yep you can use any delimeter you like (would have answered earlier but had to get a cuppa :))

This, to be honest. As soon as I saw the question, I knew I would see the correct answer in here.
 
I propose a duel to settle this!

Pivot tables at dawn!

Watch out for any exceptions (e.g. Arthur Conan Doyle). If there are any, you should be able to pick them up and correct manually.
You could knock up some VBA, and assume any multi names will be surnames to cut down the number of instances, by say putting in a hypen, then run space delimiter, then take out the hypens?
 
Last edited:
Pivot tables at dawn!

Any place any time.


You could knock up some VBA, and assume any multi names will be surnames to cut down the number of instances, by say putting in a hypen, then run space delimiter, then take out the hypens?

Depends what the requirement is - you could easily add a formula column to pull the code into a single column for instance.
 

The latest video from AVForums

TV Buying Guide - Which TV Is Best For You?
Subscribe to our YouTube channel
Back
Top Bottom