Split cell string into individual cells

Question

I am unable to split a single cell's value into two different strings and put both of those strings in different cells.

For instance I want to take a measurement 10ft x 20ft value in a cell and take the 10ft and put it in another cell, and take the 20ft and put it in a completely different cell.

I'd like to use a delimiter x or something, but I just don't know how to take those separations and do something with them after the split.

Any tips would be much appreciated. I'm still pretty new to VBA macros.

Thanks

Answer 1

You don't actually need VBA. You can use Excel's Text to Columns

For example, in excel-2010

1. Data ..... Text to Columns
2. Pick delimited and press Next
3. Check Space and put 'x' in Other and press Next
4. Finish

Answer 2

The best solution is using SPLIT

Dim strX As String
Dim sx() As String
Dim i as Integer
strX = "10FT x 20FT"
sx = Split(strX, "x")

Or maybe you can use instr function

Dim sVar1 as string
Dim sVar2 as string

I = InStr(1, strX, "x")

Now you know where can split int two variables

sVar1 = mid(strX, 1, I)
sVar2 = mid(strx,i+1)

The problem with the function is that if you have several keys in the chain with which you want to separate your function will return an array larger. For example: Dim var as string var = "x 20XP 10XP"

returns

array (0) = "10"
array (1) = "p"
array (2) = "20"
array (3) = "p"

Answer 3

Guess I just needed to look a little harder.

Sub Split_CutArea()
Dim str1() As String
Dim str2() As String
Dim avarsplit As Variant

avarsplit = Split(Cells(4, "B").Value, "x")
splitValues = Split(ActiveSheet.Cells(4, "B").Value)

ActiveSheet.Cells(22, "B").Value = splitValues(0) & splitValues(1)
ActiveSheet.Cells(23, "B").Value = splitValues(3) & splitValues(4)   
End Sub