| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns'
I believe this will do what you ask
Sub TextToColumns() Sep = InputBox("Enter the separator type", "Separator") If Sep = "" Then Exit Sub For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next End Sub Dan E "peter smith" wrote in message om... I'm trying to work out how to extract text strings of varying length from within a cell. The text instances I need to extract are all between " ". I then need to paste each piece of extracted text into cells a2, a3 ,a4 etc. I was planning to use 'text to columns' to spearate out the text but am limited by the maximum number of columns (256). Essentially what I need is something to provide the functionality of 'text to rows'. e.g. In cell a1 I have the follwoing text: "a","b","dog" and what I need to do is enter "a" in cell a2, "b" in cell a3 and "dog" in cell a4. Given that my actual data will covers more than 256 cells I can't just use text to columns, then transpose the data. Any help greatly appreciated. Peter |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns'
OOPS
Shoulda called it TextToRows?? Oh well . . . Dan E "Dan E" wrote in message ... I believe this will do what you ask Sub TextToColumns() Sep = InputBox("Enter the separator type", "Separator") If Sep = "" Then Exit Sub For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next End Sub Dan E "peter smith" wrote in message om... I'm trying to work out how to extract text strings of varying length from within a cell. The text instances I need to extract are all between " ". I then need to paste each piece of extracted text into cells a2, a3 ,a4 etc. I was planning to use 'text to columns' to spearate out the text but am limited by the maximum number of columns (256). Essentially what I need is something to provide the functionality of 'text to rows'. e.g. In cell a1 I have the follwoing text: "a","b","dog" and what I need to do is enter "a" in cell a2, "b" in cell a3 and "dog" in cell a4. Given that my actual data will covers more than 256 cells I can't just use text to columns, then transpose the data. Any help greatly appreciated. Peter |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns'
thanks v much for the response Dan E.
do you know how I could build in a 'text qualifier' option, again equivalent to the functionality of the 'text to columns' feature. basically I need to separate by commas EXCEPT where the comma is WITHIN a text string. as i have exported this data from a word form all the text is contained within " ". maybe this is getting too complicated. thanks again for any help/suggestions. Peter "Dan E" wrote in message ... OOPS Shoulda called it TextToRows?? Oh well . . . Dan E "Dan E" wrote in message ... I believe this will do what you ask Sub TextToColumns() Sep = InputBox("Enter the separator type", "Separator") If Sep = "" Then Exit Sub For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next End Sub Dan E "peter smith" wrote in message om... I'm trying to work out how to extract text strings of varying length from within a cell. The text instances I need to extract are all between " ". I then need to paste each piece of extracted text into cells a2, a3 ,a4 etc. I was planning to use 'text to columns' to spearate out the text but am limited by the maximum number of columns (256). Essentially what I need is something to provide the functionality of 'text to rows'. e.g. In cell a1 I have the follwoing text: "a","b","dog" and what I need to do is enter "a" in cell a2, "b" in cell a3 and "dog" in cell a4. Given that my actual data will covers more than 256 cells I can't just use text to columns, then transpose the data. Any help greatly appreciated. Peter |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns'
Here's a couple of functions, TextToRows and TextToCols,
Sub TextToRows() Sep = InputBox("Enter the S & the separator type or Q & the qualifier type " & vbCrLf _ & "(ie S, for comma separated or Q' for single quote qualified)", "Separator") If Sep = "" Then Exit Sub If Left(Sep, 1) = "S" Then Sep = Mid(Sep, 2, 1) DoWhat = 1 ElseIf Left(Sep, 1) = "Q" Then Sep = Mid(Sep, 2, 1) DoWhat = 2 Else MsgBox Prompt:="Invalid Entry" Exit Sub End If If DoWhat = 1 Then For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next Else For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Q1 = 1 Q2 = InStr(2, WholeLine, Sep) While Q1 = 1 TempVal = Mid(WholeLine, Q1 + 1, Q2 - Q1 - 1) Q1 = InStr(Q2 + 1, WholeLine, Sep) Q2 = InStr(Q1 + 1, WholeLine, Sep) Cell.Offset(RowNum, 0).Value = TempVal RowNum = RowNum + 1 Wend Next End If End Sub Sub TextToCols() Sep = InputBox("Enter the S & the separator type or Q & the qualifier type " & vbCrLf _ & "(ie S, for comma separated or Q' for single quote qualified)", "Separator") If Sep = "" Then Exit Sub If Left(Sep, 1) = "S" Then Sep = Mid(Sep, 2, 1) DoWhat = 1 ElseIf Left(Sep, 1) = "Q" Then Sep = Mid(Sep, 2, 1) DoWhat = 2 Else MsgBox Prompt:="Invalid Entry" Exit Sub End If If DoWhat = 1 Then For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(0, ColNum).Value = TempVal Pos = NextPos + 1 ColNum = ColNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next Else For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNum = 0 Q1 = 1 Q2 = InStr(2, WholeLine, Sep) While Q1 = 1 TempVal = Mid(WholeLine, Q1 + 1, Q2 - Q1 - 1) Q1 = InStr(Q2 + 1, WholeLine, Sep) Q2 = InStr(Q1 + 1, WholeLine, Sep) Cell.Offset(0, ColNum).Value = TempVal ColNum = ColNum + 1 Wend Next End If End Sub If the data is contained in "" it completely ignores the ,'s and separates based on the "'s Dan E "peter smith" wrote in message om... thanks v much for the response Dan E. do you know how I could build in a 'text qualifier' option, again equivalent to the functionality of the 'text to columns' feature. basically I need to separate by commas EXCEPT where the comma is WITHIN a text string. as i have exported this data from a word form all the text is contained within " ". maybe this is getting too complicated. thanks again for any help/suggestions. Peter "Dan E" wrote in message ... OOPS Shoulda called it TextToRows?? Oh well . . . Dan E "Dan E" wrote in message ... I believe this will do what you ask Sub TextToColumns() Sep = InputBox("Enter the separator type", "Separator") If Sep = "" Then Exit Sub For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next End Sub Dan E "peter smith" wrote in message om... I'm trying to work out how to extract text strings of varying length from within a cell. The text instances I need to extract are all between " ". I then need to paste each piece of extracted text into cells a2, a3 ,a4 etc. I was planning to use 'text to columns' to spearate out the text but am limited by the maximum number of columns (256). Essentially what I need is something to provide the functionality of 'text to rows'. e.g. In cell a1 I have the follwoing text: "a","b","dog" and what I need to do is enter "a" in cell a2, "b" in cell a3 and "dog" in cell a4. Given that my actual data will covers more than 256 cells I can't just use text to columns, then transpose the data. Any help greatly appreciated. Peter |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns'
Peter you might want to change the lines
While Q1 = 1 TO While Q1 = 1 And Q2 = 1 Dan E "Dan E" wrote in message ... Here's a couple of functions, TextToRows and TextToCols, Sub TextToRows() Sep = InputBox("Enter the S & the separator type or Q & the qualifier type " & vbCrLf _ & "(ie S, for comma separated or Q' for single quote qualified)", "Separator") If Sep = "" Then Exit Sub If Left(Sep, 1) = "S" Then Sep = Mid(Sep, 2, 1) DoWhat = 1 ElseIf Left(Sep, 1) = "Q" Then Sep = Mid(Sep, 2, 1) DoWhat = 2 Else MsgBox Prompt:="Invalid Entry" Exit Sub End If If DoWhat = 1 Then For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next Else For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Q1 = 1 Q2 = InStr(2, WholeLine, Sep) While Q1 = 1 TempVal = Mid(WholeLine, Q1 + 1, Q2 - Q1 - 1) Q1 = InStr(Q2 + 1, WholeLine, Sep) Q2 = InStr(Q1 + 1, WholeLine, Sep) Cell.Offset(RowNum, 0).Value = TempVal RowNum = RowNum + 1 Wend Next End If End Sub Sub TextToCols() Sep = InputBox("Enter the S & the separator type or Q & the qualifier type " & vbCrLf _ & "(ie S, for comma separated or Q' for single quote qualified)", "Separator") If Sep = "" Then Exit Sub If Left(Sep, 1) = "S" Then Sep = Mid(Sep, 2, 1) DoWhat = 1 ElseIf Left(Sep, 1) = "Q" Then Sep = Mid(Sep, 2, 1) DoWhat = 2 Else MsgBox Prompt:="Invalid Entry" Exit Sub End If If DoWhat = 1 Then For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(0, ColNum).Value = TempVal Pos = NextPos + 1 ColNum = ColNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next Else For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If ColNum = 0 Q1 = 1 Q2 = InStr(2, WholeLine, Sep) While Q1 = 1 TempVal = Mid(WholeLine, Q1 + 1, Q2 - Q1 - 1) Q1 = InStr(Q2 + 1, WholeLine, Sep) Q2 = InStr(Q1 + 1, WholeLine, Sep) Cell.Offset(0, ColNum).Value = TempVal ColNum = ColNum + 1 Wend Next End If End Sub If the data is contained in "" it completely ignores the ,'s and separates based on the "'s Dan E "peter smith" wrote in message om... thanks v much for the response Dan E. do you know how I could build in a 'text qualifier' option, again equivalent to the functionality of the 'text to columns' feature. basically I need to separate by commas EXCEPT where the comma is WITHIN a text string. as i have exported this data from a word form all the text is contained within " ". maybe this is getting too complicated. thanks again for any help/suggestions. Peter "Dan E" wrote in message ... OOPS Shoulda called it TextToRows?? Oh well . . . Dan E "Dan E" wrote in message ... I believe this will do what you ask Sub TextToColumns() Sep = InputBox("Enter the separator type", "Separator") If Sep = "" Then Exit Sub For Each Cell In Selection WholeLine = CStr(Cell.Value) If Right(WholeLine, 1) < Sep Then WholeLine = WholeLine & Sep End If RowNum = 0 Pos = 1 NextPos = InStr(Pos, WholeLine, Sep) While NextPos = 1 TempVal = Mid(WholeLine, Pos, NextPos - Pos) Cell.Offset(RowNum, 0).Value = TempVal Pos = NextPos + 1 RowNum = RowNum + 1 NextPos = InStr(Pos, WholeLine, Sep) Wend Next End Sub Dan E "peter smith" wrote in message om... I'm trying to work out how to extract text strings of varying length from within a cell. The text instances I need to extract are all between " ". I then need to paste each piece of extracted text into cells a2, a3 ,a4 etc. I was planning to use 'text to columns' to spearate out the text but am limited by the maximum number of columns (256). Essentially what I need is something to provide the functionality of 'text to rows'. e.g. In cell a1 I have the follwoing text: "a","b","dog" and what I need to do is enter "a" in cell a2, "b" in cell a3 and "dog" in cell a4. Given that my actual data will covers more than 256 cells I can't just use text to columns, then transpose the data. Any help greatly appreciated. Peter |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extracting certain text from text string | Excel Worksheet Functions | |||
| Extracting text from a cell | Excel Discussion (Misc queries) | |||
| Each rows last cell text value could be in any columns. | Excel Worksheet Functions | |||
| Extracting text from text string | Excel Worksheet Functions | |||
| Extracting Text in a string of text | Excel Discussion (Misc queries) | |||
![Dan E[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
