Test separation via Excel

[ptonsparky]

I need to separate text in an Excel SS.

data: Last Name, First: 22345-job description

I would like to separate at the :

Results would be Last Name, First in one cell with the rest in the second. Is some one up to telling me what @function that would be? I think I have done it before but CRS is more significant every day.

 

[ptonsparky]

Text...

 

[Ken_S]

A table would allow you to filter data

 

[petersonra]

You have to use the find function to find where in the string the colon is first.

=LEFT(A2,FIND(":",A2),-1)

Where the string you want to search is in cell a2.

Iirc, the -1 returns all characters to the left of the :.

 

[Hv&Lv]

On the data tab select text to columns, then select your delimiters. For yours it would be colon

 

[Dennis Alwon]

Here is a blow by blow description

How to Separate Names in Excel

See how to separate first, middle, and last names in Excel. Includes Convert Text to Columns, formulas, and Flash Fill solutions.

www.timeatlas.com

 

[ptonsparky]

'Duh
Text to Columns'
Too easy and quick.

I look at the Data tab every morning and have missed that every morning.

 

[retirede]

'Duh
Text to Columns'
Too easy and quick.

I look at the Data tab every morning and have missed that every morning.

Earlier versions called it “Parse” which made perfect sense to me. I don’t know why it got changed to “Text to columns”???

 

[ptonsparky]

Earlier versions called it “Parse” which made perfect sense to me. I don’t know why it got changed to “Text to columns”???

Parse does sound familiar. It has been awhile since I last needed it.

 

[Dennis Alwon]

I remember using a formula but I don't remember what it was.. Parse doesn't sound familiar. Smart$ was really good at this stuff... I miss him.

 

[retirede]

I remember using a formula but I don't remember what it was.. Parse doesn't sound familiar. Smart$ was really good at this stuff... I miss him.

Parse was renamed text to columns sometime about Office 97. So it’s been quite a while ago.

 

[ptonsparky]

Parse was renamed text to columns sometime about Office 97. So it’s been quite a while ago.

Good grief, how do you guys remember that stuff?

 

[Hv&Lv]

I remember using a formula but I don't remember what it was.. Parse doesn't sound familiar. Smart$ was really good at this stuff... I miss him.

No need for a formula.. see post 5

 

[Dennis Alwon]

No need for a formula.. see post 5

I know that. I was saying we used to use formulas. Life gets easier and easier when the new editions roll out.

 

[Dennis Alwon]

I just learned an interesting method

Col A has the data-- first name last name
In col B type the first name hit enter and go to the cell below and type ctrl "E" .. It will copy all the first names
Do the same for the last names.

 

[retirede]

Good grief, how do you guys remember that stuff?

If only I could remember where I left my car keys….