Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need Excel count of 1 word if found in multi-word cells of column | Excel Worksheet Functions | |||
Length that falls within a length interval? | Excel Worksheet Functions | |||
Finding a string of unknown length in a string of unknown length, Help! | Excel Discussion (Misc queries) | |||
convert second word in range to Proper when first variable length -urgent please | Excel Programming | |||
Validation length, Range length | Excel Discussion (Misc queries) |