Splitting text in one cell to multiple cells

[hobman]

I have contact person information that is exported into excel. The Experience field has multiple experiences in one cell and they are separated by a period. I would like a macro that will split the experiences and cut and paste each one in difference cells. Possibly by inserting a new cell below for each experience.

I don’t know if this is clear.

Thanx

 

[VBAjedi]

This may not require any code at all. Are you familiar with Text To Columns? On the Data menu, choose Text To Columns. Choose the "Delimited" option, select Next, and specify a period as your delimiter. That will split the data segments out into columns.




VBAjedi

 

[SkipVought]

Why not use Text to Columns with a PERIOD for a delimiter?

Macro record it and VOLA!

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[Molby]

You don't need a macro for this, you can use the Text to columns function. Simply highlight the data you want to split, then go to Data, Text To Columns. Highlight the Delimited option, then in the other box, type in a period. You should see at the bottom your data now has lines through it where the periods used to be. Click next to determine the data type of each of the columns, and then click finish.

 

[Molby]

Blimey, and I thought I typed that pretty fast!!

 

[hobman]

Thanx VBAjedi & SkipVought, I had figured that but I really need it to copy to the cell below. Any VBA ideas?

Thanx

 

[Molby]

You can select the destination when you choose the text to columns.

 

[VBAjedi]

Be aware that when you split a one-row record into multiple rows, you run a big risk of falling afoul of a number of different Excel functions. For example, accidentally or intentionally sorting the data can be very difficult to undo later because the rows that are related to each other are no longer next to each other.

That said, post a sample record of your data for us to look at. Also describe what the finished sheet should look like. For example, when the contact information for one person ends (presumably after one primary row followed by several rows of "experiences"), does the information for another person start immediately? Or is there a blank row in between? Or do you only have information for one contact on this sheet?



VBAjedi

 

[hobman]

here is a sample:

Name SSN Experience

Joh Doe 6666 1999 Sears. 2000 Home Depot.
2003 CNN.

Bob Dole 5555 1990 NBC. 1995 PwC. 2000 Deloitte

I wante it to look like

Name SSN Experience

Jon Doe 6666 1999 Sears
Jon Doe 6666 2000 Home Depot
Jon Doe 6666 2003 CNN
Bob Dole 5555 1990 NBC
Bob Dole 5555 1995 PwC
Bob Dole 5555 2000 Deloitte

 

[SkipVought]

Are you stating that on SOME records there are multiple rows, such as
[tt]
Joh Doe 6666 1999 Sears. 2000 Home Depot.
2003 CNN.
[/tt]

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[hobman]

No, there are multiple records in one cell

 

[SkipVought]

1) Text to columns Col A - Fixed width (to get Name/SSN/Experience)

2) Text to columns Col C - Delimitederiod (to parse experience)

3) Select all columns and sort (to eliminate enpty rows)

4) Table 1
[tt]
Name SSN
Bob Dole 5555
Joh Doe 6666
[/tt]
5) Interim Table 2
[tt]
Name Experience
Bob Dole 1990 NBC 1995 PwC 2000 Deloitte
Joh Doe 1999 Sears 2000 Home Depot 2003 CNN
[/tt]
select interim table 2, Data/PivotTable, Multiple consolidation ranges, I will create the page fields, with cursor in the Range box, select ALL data in table 2, [ADD], [Layout]-drag the ROW & COLUMN buttons OFF the layout, [Finish]

6) You see a 4x4 PivotTable -- double click the LOWER RH cell

7) The result Table 3
[tt]
Row Column Value
Bob Dole Experience 1990 NBC
Bob Dole 1995 PwC
Bob Dole 2000 Deloitte
Joh Doe Experience 1999 Sears
Joh Doe 2000 Home Depot
Joh Doe 2003 CNN
[/tt]
8) Delete Column B, rename headings.

VOLA!

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}

 

[SkipVought]

BTW this FAQ explains this prcess better

NORMALIZE Your Table using the PivotTable Wizard faq68-5287

Skip,

_{[red]Be advised:[/red] When transmitting sheet music...
If it ain't baroque, don't fax it!}