length of word
 

I have a column of data that read like

Glass Bacchus
Glass Alverdi


I want to first copy the "glass" text and put that info in another column

and then I want to delete the "glass" text so there are no spaces before the
Bacchus or Alverdi word

thank you

length of word
 

hi
assuming that your example data is characteritic of all your other data then
the easiest way would be text to columns
on the menu bardatatext to columns
select delimitedspace
make sure that you add a blank column next to your data. text to columns
will overwrite the data in the next column.

Regards
FSt1

"dstiefe" wrote:

I have a column of data that read like

Glass Bacchus
Glass Alverdi


I want to first copy the "glass" text and put that info in another column

and then I want to delete the "glass" text so there are no spaces before the
Bacchus or Alverdi word

thank you

length of word
 

If your data is in Column A:

in Column B, add the formula =LEFT(A1,FIND(" ",A1)-1)
(this separates out the "Glass" part)

in Column C, add the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1))
(this gets the rest of the text)

Then fill down as many rows as you have data.

This will work no matter what the first word (Glass in this case) is - it
will always separate the first word from the rest of the cell's text.

HTH,

Eric

length of word
 

I thought about this, but assumed that the second part of his text could have
multiple words - it's obviously a brand or company name, so text to columns
would put the rest of the cell text into multiple columns.

"FSt1" wrote:

hi
assuming that your example data is characteritic of all your other data then
the easiest way would be text to columns
on the menu bardatatext to columns
select delimitedspace
make sure that you add a blank column next to your data. text to columns
will overwrite the data in the next column.

Regards
FSt1

"dstiefe" wrote:

I have a column of data that read like

Glass Bacchus
Glass Alverdi


I want to first copy the "glass" text and put that info in another column

and then I want to delete the "glass" text so there are no spaces before the
Bacchus or Alverdi word

thank you

length of word
 

Sub splitum()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
With Cells(i, 1)
s = Split(.Value, " ")
.Value = s(1)
.Offset(0, 1).Value = s(0)
End With
Next
End Sub

--
Gary''s Student - gsnu200835


"egun" wrote:

If your data is in Column A:

in Column B, add the formula =LEFT(A1,FIND(" ",A1)-1)
(this separates out the "Glass" part)

in Column C, add the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1))
(this gets the rest of the text)

Then fill down as many rows as you have data.

This will work no matter what the first word (Glass in this case) is - it
will always separate the first word from the rest of the cell's text.

HTH,

Eric

length of word
 

LOL - always so many ways to do the job!
-------------------------
If toast always lands butter-side down, and cats always land on their feet,
what happen if you strap toast on the back of a cat and drop it?
Steven Wright (1955 - )


"Gary''s Student" wrote:

Sub splitum()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
With Cells(i, 1)
s = Split(.Value, " ")
.Value = s(1)
.Offset(0, 1).Value = s(0)
End With
Next
End Sub

--
Gary''s Student - gsnu200835


"egun" wrote:

If your data is in Column A:

in Column B, add the formula =LEFT(A1,FIND(" ",A1)-1)
(this separates out the "Glass" part)

in Column C, add the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1))
(this gets the rest of the text)

Then fill down as many rows as you have data.

This will work no matter what the first word (Glass in this case) is - it
will always separate the first word from the rest of the cell's text.

HTH,

Eric

length of word
 

Your code will only work if there is exactly two words in each cell. On the off chance there could be less or more, here is some code that will move the first word into the next column and keep the remainder of the words (if any) in the current column and it will also skip over empty cells if any...

Sub SplitAndSwap()
Dim X As Long
Dim LastRow As Long
Dim Parts() As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
On Error GoTo Continue
For X = 1 To LastRow
With Cells(X, 1)
Parts = Split(.Value)
.Offset(0, 1).Value = Parts(0)
Parts(0) = ""
.Value = Trim(Join(Parts))
End With
Continue:
Next
End Sub

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message ...
Sub splitum()
n = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To n
With Cells(i, 1)
s = Split(.Value, " ")
.Value = s(1)
.Offset(0, 1).Value = s(0)
End With
Next
End Sub

--
Gary''s Student - gsnu200835


"egun" wrote:

If your data is in Column A:

in Column B, add the formula =LEFT(A1,FIND(" ",A1)-1)
(this separates out the "Glass" part)

in Column C, add the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1))
(this gets the rest of the text)

Then fill down as many rows as you have data.

This will work no matter what the first word (Glass in this case) is - it
will always separate the first word from the rest of the cell's text.

HTH,

Eric