Splitting up field into separate cells

[PEASLET]

Hi, yesterday I had a problem merging cells; today I have the problem where I need to import a name field where the data has the whole name in one field and my table has Title, Initial, FirstName LastName etc., so the cell needs to be split up.

JVFriederick - are you there?? Or can anyone help?
Thanks

 

[PEASLET]

It's OK, I've done it using text to columns - quite a bit of editing to do but never mind....

What's a 'floater' by the way??

 

[JVFriederick]

A "floater" is a question that's easy to "pick off", like a pass thrown by a Chicago quarterback.

Copy and paste one or two examples of your data.
It can likely be parsed with the MID formula.

 

[PEASLET]

I've done it now - however I've noticed the address field is similar...

Copley Bent, Butterknowle
Windmill
Dengate Lane, Staindrop
Lartington
Hamsterley
Cross Lanes
Holwick Road, Middleton-in-Teesdale
Brignall
Dentgate Lane, Langleydale

The ones with commas obviously need to be in separate columns without commas ....I can save it as text, import to Access and separate them that way but if you know of something quicker, please let me know!!

Thanks - do you want another star or do you just give stars for the real toughies??!!!

 

[JVFriederick]

A quicker way might be to let Excel search for records that have commas in them (you need to deal with them manually anyway).

Add the formula in an empty column :
=FIND(",",A1) This will return a number if a comma is found.

Now sort the records so all the rows with commas are together.

If you have many records with commas, you can further parse it with FIND and LENGTH, but I don't want to take the time to write the equation unless you need it. Let me know if you do.

 

[ETID]

Have you tried the "text to columns" tool under the data menu?

 

[JVFriederick]

I think that was mentioned in the 2nd posting.