Reverse Concatenation (Splitting Single Cell Data into Multiple Ce

[SirEric]

Good Sirs & Madams:

Please help me with my excel question. Thank you in advance.

1002bat
2003cat
3009dog
4005frog
5003snake

I want to take single cell data and split the contents into multiple cells.

"1002bat" would become "100", "2", "Bat".

The way our information is "imported" into excel results in the
concatenation of data that would be useful to be split.

Is there a macro that can take the first "x" digits/text" of a long string
in order to accomplish this separation.

Thanks,

Sir Eric
(Queen's Knight)

 

[Ragdyer]

Select the column of data, then, from the Menu Bar,
<Data> <Text To Columns> <Fixed Width> <Next>

Follow the instructions in the Wizard and create the break lines where
necessary.

Post back with any questions.

 

[Chip Pearson]

You can try code like the following. First, select the cells containing the
data to split. The split components will be in the 3 columns to the right of
the original data. When you run the code, you will be prompted for the
length of the first two components to split, separated by a comma. For
example, to split after into 3 characters then 1 characters, enter '3,1'.
The third split text is the remaining text on the right, regardless of its
length.

Sub AAAA()

Dim S As String
Dim N As Long
Dim V As Variant
Dim R As Range
Dim S1 As String
Dim S2 As String
Dim S3 As String

S = InputBox("Enter data widths separated by commas")
If S = vbNullString Then
Exit Sub
End If
S = Replace(S, " ", vbNullString)
V = Split(S, ",")
For N = LBound(V) To UBound(V)
If IsNumeric(V(N)) = False Then
MsgBox "Non numeric entry is invalid"
Exit Sub
End If
Next N
If UBound(V, 1) - LBound(V, 1) + 1 <> 2 Then
MsgBox "Invalid data"
Exit Sub
End If
If Not TypeOf Selection Is Excel.Range Then
MsgBox "Selection is not a range"
Exit Sub
End If
If Selection.Columns.Count > 1 Then
MsgBox "Selection must be only one column"
Exit Sub
End If
For Each R In Selection.Cells
S1 = vbNullString
S2 = vbNullString
S3 = vbNullString
S1 = Left(R.Text, CInt(V(0)))
S2 = Mid(R.Text, CInt(V(0)) + 1, CInt(V(1)))
S3 = Mid(R.Text, CInt(V(0)) + CInt(V(1)))
R(1, 2) = S1
R(1, 3) = S2
R(1, 4) = S3
Next R
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Rick Rothstein \(MVP - VB\)]

You didn't give us the "rule" for the rest of your splits, but to answer the
question you asked... you would use the LEFT function. Its syntax is

=LEFT(TextString,NumberOfCharacterFromLeftToGet)

So, if your data is in A1, then the leftmost 3 characters would be
=LEFT(A1,3). There is an equivalent RIGHT function if you know how many
characters you want to get from the rightside of the text string. To get a
variable number of characters from anywhere within the text string, you
would use the MID function. Its syntax is this...

=MID(TextString,StartPosition,NumberOfCharacters)

So, if you wanted the internal 3 characters starting at character position
4, you would use =MID(A1,4,3). To get the remainder of the text string
starting at, say, character position 5, you could to this...

=MID(A1,5,LEN(A1)-4)

where the 4 in the subtraction is one less than the start position. Most
people, though, would simply do something like this instead....

=MID(A1,5,99)

where, knowing the structure of their data, they would substitute for my
example length number of 99 a number larger than the maximum possible length
of the text string being parsed.

Rick

 

[Stephen McLeod]

Rick,

I thought your explanation on this subject very clear - I have 2 related questions and a 3rd separate but frustrating very different issue.

1. How can I take a long selection of cells and remove in every case the last character?

2. Is there a way in a long selection of data I can eliminate all characters in a string to the left of or right of a single character or more usefully a common series of characters in a string? eg., if the word "Contact" appears in every string how can i get split the data so that everything before the word "Contact"is separated?

3. For some reason in Excel for the up/down/left/right keys for navigating in a worksheet do not work and only have the effect of moving the entire worksheet up/down/left or right on the screen. Not sure what I have done but can't find the fools solution.

Thanks

Stephen

 

[Daniel.C]

1. How can I take a long selection of cells and remove in every case the last

character?

Sub test1()
Dim c As Range
For Each c In Selection
c.Value = Left(c.Value, Len(c.Value) - 1)
Next c
End Sub

2. Is there a way in a long selection of data I can eliminate all characters
in a string to the left of or right of a single character or more usefully a
common series of characters in a string? eg., if the word "Contact" appears
in every string how can i get split the data so that everything before the
word "Contact"is separated?

Sub test2()
Dim c As Range
For Each c In Selection
If c.Value Like "*Contact*" Then
c.Value = Right(c.Value, Len(c.Value) - InStr(1, c.Value,
"Contact") + 1)
End If
'if instr(1,c.Value,contact = Left(c.Value, Len(c.Value) - 1)
Next c
End Sub

HTH
Daniel