Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
CELL CONTENTS SEPARATION
Is there any shortcut to separate (text and number - like ABCDE123456) of a
single cell?. Especially if the no. of digits are not constant. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
CELL CONTENTS SEPARATION
I use 2 custom functions to parse out number/text from a string.
Press Alt + F11 to open the VBA module, click INSERT/NEW/MODULE and paste the following 2 functions into the empty module. Each function starts with 'Function and ends with 'End Function' Function ExtractNumbers(varVal As Variant) As Long Dim intLen As Integer Dim strVal As String Dim i As Integer Dim strChar As String Dim intChar As Integer intLen = Len(varVal) Application.Volatile For i = 1 To intLen strChar = Mid$(varVal, i, 1) intChar = Asc(strChar) Select Case intChar Case 1 To 47 Case 48 To 57 strVal = strVal & strChar Case 58 To 255 Case Else End Select Next i If Len(strVal) = 0 Then ExtractNumbers = 0 Else ExtractNumbers = CLng(strVal) End If End Function Function ExtractAlpha(varVal As Variant) As String Dim intLen As Integer Dim strVal As String Dim i As Integer Dim strChar As String intLen = Len(varVal) For i = 1 To intLen strChar = Mid$(varVal, i, 1) If Asc(strChar) = 65 And Asc(strChar) <= 90 Or _ Asc(strChar) = 97 And Asc(strChar) <= 122 Then strVal = strVal & strChar End If Next i ExtractAlpha = strVal End Function Hope this helps... -- Kevin Backmann "Tazeem" wrote: Is there any shortcut to separate (text and number - like ABCDE123456) of a single cell?. Especially if the no. of digits are not constant. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
CELL CONTENTS SEPARATION
Try one of the answers you were given the last time you asked the question.
-- David Biddulph "Tazeem" wrote in message ... Is there any shortcut to separate (text and number - like ABCDE123456) of a single cell?. Especially if the no. of digits are not constant. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
CELL CONTENTS SEPARATION
Dear Mr. David,
I have tried but the results based on some specific condition of delimited with any character such as commas, tabs or fixed numbers etc. Whereas, there is no fixed no. of digits in the data I have. Need help. Regards, "David Biddulph" wrote: Try one of the answers you were given the last time you asked the question. -- David Biddulph "Tazeem" wrote in message ... Is there any shortcut to separate (text and number - like ABCDE123456) of a single cell?. Especially if the no. of digits are not constant. Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
CELL CONTENTS SEPARATION
Ron Rosenfeld showed you solutions using REGEX and Longre's free
morefunc.xll add-in muddan madhu showed you the array formulae =MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1 )),0),COUNT(1*MID(A1,ROW($1:$20),1))) and =MID(A1,ROW($1:$20),LEN(A1)-COUNT(1*MID(A1,ROW($1:$20),1))) It's no good coming back and repeatedly asking the same question if you don't explain what was wrong with the previous answers you've had. -- David Biddulph "Tazeem" wrote in message ... Dear Mr. David, I have tried but the results based on some specific condition of delimited with any character such as commas, tabs or fixed numbers etc. Whereas, there is no fixed no. of digits in the data I have. Need help. Regards, "David Biddulph" wrote: Try one of the answers you were given the last time you asked the question. -- David Biddulph "Tazeem" wrote in message ... Is there any shortcut to separate (text and number - like ABCDE123456) of a single cell?. Especially if the no. of digits are not constant. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separation of text and numbers in an unmerged cell | Excel Discussion (Misc queries) | |||
Mean separation by LSD value | Excel Worksheet Functions | |||
Cell Separation | Excel Worksheet Functions | |||
Macro to remove contents of cell and move all other contents up one row | Excel Discussion (Misc queries) | |||
Cell contents vs. Formula contents | Excel Discussion (Misc queries) |