Split Cell Content Into Two Columns

[adamsm]

Hi,

I'm trying to write a formula or VBA code that would split up the contents of the column "I" of the active sheet when the user writes age the same column "I".

Suppose, if the user writes "29 Years" in column "I" the formula or code would split them and place the "29" in column "L" and "Years" in column "M".

I hope I've made my question clear.

Any help on this would be kindly appreciated.

Thanks in advance.

[BrianMH]

Just use formulas.

=LEFT(A1,FIND(" ",A1,1)-1)
=RIGHT(A1,LEN(A1)-FIND(" ",A1,1))

adjust for your cells.

[adamsm]

Thanks for the reply. But I guess to use the formula I should be placing the "Years" with FIND(" "). Right?

I mean I have so many rows of data. With this scenario how could I adjust the formula to palace the text withing the "".

I have text as years or days and even as months.

Any help on this would be kindly appreciated.

[BrianMH]

Don't need to replace anything. You just point the formulas to the cells and copy down.

Try this in a blank workbook so you can see how it works.

type 29 years in cell A1
put formula =LEFT(A1,FIND(" ",A1,1)-1) in B1
put formula =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) in B2

result

[adamsm]

Once again thanks for the help. If I may ask for one more help How should I change the code to a worksheet event code so that when the user writes the age in column I the formula gets copied to columns L and M.

Any help on this would be kindly appreciated.

[mancubus]

hi.
google "vba split function"


an example for col K


Private Sub Worksheet_Change(ByVal Target As Range)

Dim splitStr

If Intersect(Target, Range("K:K")) Is Nothing Then Exit Sub

splitStr = Split(Target, " ") 'delimiter is "space"

Target.Offset(, 1).Resize(1, UBound(splitStr) + 1) = splitStr

End Sub

[mancubus]

i think this one suits your case.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim splitStr

If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
splitStr = Split(Target, " ")
Target.Offset(, 3).Resize(1, UBound(splitStr) + 1) = splitStr

End Sub