Split data from cells

[jimonfly]

Due to an exporting problem with a program I am using for lease analysis, I am getting data from three fields exporting into one cell during a CSV export. This then throws off all of my other columns off by 2 cells.

Is there a way to split the information in this cell into three cells so I can import the data into a predefined table?

If it matters, the original fields are a suite size (number), start date (MM/DD/YY) and expiration date (MM/DD/YY). The exported cells contain the number, followed by 5 spaces, the date, followed by four spaces. This changes if the month is a single digit, in which case the space count is increased by one.

 

[VoG]

Select the data, Data > Text to Columns, select Delimited, specify Space as the delimiter, check Treat successive delimiters as one then click Finish.

 

[jimonfly]

Thanks, that's what I needed. Is there a way to have it insert cells and push the data to the right? The only option I see is to overwrite the data in the existing cells. If not, I can probably work something out in VBA.

Jim

 

[VoG]

Try selecting the cells, running the macro below, then doing Text to Columns

Sub InsCols()
Selection.Offset(, 1).Resize(, 2).Insert shift:=xlToRight
End Sub

 

[jimonfly]

Thanks.