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

score 5 · Accepted Answer · answered Oct 22 '12 at 21:34

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"

Thanks I'll try that variation as well. – cheapkid1 Oct 22 '12 at 21:39 — cheapkid1, Oct 22 '12 at 21:39

score 5 · Answer 2 · answered Oct 22 '12 at 23:09

5

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

For example, in excel-2010

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

enter image description here

answered Oct 22 '12 at 23:09

brettdj

54,857
16
114
177

1

Wow, that is handy. I had no idea that was there. Shows how much I know. However since I'm automating this after I get the manual portion working, I will be using VBA to just do it for me. – cheapkid1 Oct 23 '12 at 14:22
@cheapkid1 If **Text to Columns** works then you can use that from VBA as well. – Zev Spitz Aug 28 '13 at 05:56
https://learn.microsoft.com/en-us/office/vba/api/excel.range.texttocolumns – HackSlash Dec 10 '20 at 22:55

score 1 · Answer 3 · edited Oct 22 '12 at 23:02

1

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

edited Oct 22 '12 at 23:02

brettdj

54,857
16
114
177

answered Oct 22 '12 at 21:36

cheapkid1

469
7
18
32

Thanks, that other 'End Sub' was a part of a Public Class, so I was just showing the part that applied, next time I'll be more carefull to delete what doesn't belong. – cheapkid1 Oct 23 '12 at 14:25

Split cell string into individual cells

3 Answers3

Linked

Related