Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
separating text and numbers into different columns
If I have sv14 in a cell, is there a formula I can use to copy the text characters into one column and the number into another? The number of text characters and number of numerical digits could vary. (however it is a limited list of text characters that would proceed the digits. It would always start with one of the following:
b v h s d db e s sv sh c cv ch f pb pk po code (yes "code" is one of the text strings) Thank you in advance! Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
separating text and numbers into different columns
Hi Paul,
If there is no decimal Function TXT(cel As Range) Dim x As String, i As Integer, n For i = 1 To Len(cel) x = Asc(StrConv(Mid(cel, i, 1), vbLowerCase)) If x = 97 And x <= 122 Then t = t & Mid(cel, i, 1) End If Next TXT = t End Function Function NUMBER(cel As Range) Dim x As String, i As Integer, n For i = 1 To Len(cel) x = Asc(StrConv(Mid(cel, i, 1), vbLowerCase)) If x = 49 And x <= 57 Then n = n & Mid(cel, i, 1) End If Next NUMBER = n * 1 End Function isabelle Le 2017-02-20 Ã* 18:23, Paul Doucette a écrit : If I have sv14 in a cell, is there a formula I can use to copy the text characters into one column and the number into another? The number of text characters and number of numerical digits could vary. (however it is a limited list of text characters that would proceed the digits. It would always start with one of the following: b v h s d db e s sv sh c cv ch f pb pk po code (yes "code" is one of the text strings) Thank you in advance! Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
separating numbers from text | Excel Worksheet Functions | |||
Separating numbers and words into separate columns | Excel Discussion (Misc queries) | |||
Separating Text From Numbers | Excel Discussion (Misc queries) | |||
separating +ve and -ve numbers into two columns | Excel Discussion (Misc queries) | |||
Help - Separating numbers appearing on the right of a text string | Excel Worksheet Functions |