Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Concatenation (Splitting Single Cell Data into Multiple Ce
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Concatenation (Splitting Single Cell Data into Multiple Ce
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. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "SirEric" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Concatenation (Splitting Single Cell Data into Multiple Ce
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) "SirEric" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Reverse Concatenation (Splitting Single Cell Data into Multiple Ce
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 "SirEric" wrote in message ... 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) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting cell data into multiple cell information
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting cell data into multiple cell information
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REVERSE SINGLE CELL TEXT STRING | Excel Worksheet Functions | |||
Splitting data in a single cell that is seperated by commas, then moving to make individual rows | Excel Discussion (Misc queries) | |||
Splitting data out of a single cell | Excel Discussion (Misc queries) | |||
Splitting single column data into two | Excel Worksheet Functions | |||
Splitting a single cell | Excel Discussion (Misc queries) |