Create four columns out of one

H

Hasse

Hi,

I have a string that looks like this,
"EC330-360_D10CA010119_Carl Axling_2001-05-10.doc".
From that string I would like to create four columns like this,
EC330-360 D10CA010119 John Smith 2001-05-10
How do I put these four strings in separate columns?

Regards,
Hans
 
S

SVC

Use Data > Text to Columns, select Delimited, click Next and select Other and
type an underscore (_), then click Next to format the columns or Finish if
yo do not wnat to change any formatting of the columns.
 
D

Dave Peterson

How do you get John Smith from Carl Axling <vbg>?

I'd select that column with all those entries (or just that one cell)
and do data|text to columns
delimited
by an underscore (_)

Then I'd select that last column and...
Edit|Replace
what: .doc
with: (leave blank)
replace all

And be prepared to reformat that column the way you want the date to look.
 
S

Sloth

Select the column/cells you wish to split.
Data->Text to Columns
Select Delimited, and click next
Select Other, put a _ in the box, and click finish

Be sure there is no info to the right or they will be overwritten. You
might have to do it again to take of the .doc section, and then delete that
section, or you could do a Find and Replace replacing .doc with nothing
(leave replace box blank) before you split your data.
 
B

B. R.Ramachandran

Hi,

Let's suppose that the strings are in Column A.
Select the entire column --> "Data" --> "Text to Columns" --> select
"Delimited" --> "Next" --> select "Other" and enter underscore character
("_")--> "Next" --> "Finish".
The string would now be distributed in four columns. The last column
(Column D) would contain strings such as "2001-05-10.doc".

Now select the last column, repeat the above procedure using "." instead of
"_" for "Other". This would separate the last part into two columns (D and
E) , e.g., "2001-05-10" and "doc". Delete the very last column.

Regards,
B. R. Ramachandran
 
H

Hasse

Hi again,

Sorry I wasn't clear enough.
I would like to do this in a formula that I can use in a SharePoint document
library.
And of course, Carl Axling is not changing his name to John Smith. Don't
know how he came into the picture. ;-)

Regards,
Hans
 
Top