Splitting Info from one column to another

[Leesha]

Hi,
I feel like I'm always asking this question. No matter how many times I try to do it using old info I fail. :-( I need to do the following. Column D contains info in this format:

Doe, J/111111

This is the last name, comma, first initial/record number. The forward slash is the one character that is consistent. The name info and record info will be of varying lengths. I need to separate the record number and last name, first initial into two separate columns.

Thanks,
Leesha

[HansV]

Select column D.
Activate the Data tab of the ribbon.
In the Data Tools group, click Text to Columns.
Select Delimited, then click Next >.
Clear all the check boxes under Delimiters, then enter / in the box next to Other (this will automatically tick the Other check box).
Click Finish.

[Leesha]

Hi Hans!
That worked like a charm and so easy on the brain!

I will be automating this spreadsheet for others to use. They will be running a macro to split the info. I recorded what I did. Is there a place below to stop the popup stating that there is already information in the cell and do I want to replace it?

Columns("E:E").Select
Selection.Insert Shift:=xlToRight
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Thanks,
Leesha

[HansV]

You already insert a new column, so you shouldn't get a pop up.
Or do I misunderstand?

[Leesha]

Oh, tried it both ways. Whether I insert the column first or don't do it I get the message re data being there.

[HansV]

Do some of the cells in column D perhaps contain more than one slash? That would explain the message.

[Leesha]

I checked the file I'm testing with and that isn't the case. There is only one / in each cell. I was thinking it was because the person's name is being put into column D and the number is put into the new column (whether I create it in advance or the wizard creates it). Since the original code is in Column D and is being replaced with the person's name, I'm being prompted that there is already data there and do I want to replace it. If I don't put in a new blank column, the info that is currently in column E gets replaced with the record number.

[HansV]

It doesn't work that way for me. But you can try inserting

Application.DisplayAlerts = False

above the Selection.TextToColumn instruction, and

Application.DisplayAlerts = True

below it.

[Leesha]

That did it!!
Thanks as always,
Leesha