SEPERATE TEXT NUMBER

[parbatidebnath]

INPUT OUTPUT

CELL A1 CELL B1 CELL C1

12A3BC 123 ABC HOW TO SEPERATE NUMBER AND TEXT


THANKS & REGARDS,

Parbati Debnath

 

[asowdi]

Deb,

Check out this thread, it may solve 50% of your problem

http://chandoo.org/forums/topic/separate-number-from-text-thru-formula

 

[parbatidebnath]

please give an example with this formula

 

[bobhc]

Good day parbatidebnath


Option1: From menu Data>Text to columns>Space delimiter..


Option2: (If space between name and number) With data in cell A1; in cell B1

enter the formula

=MID(A1,FIND(CHAR(32),A1)+1,LEN(A1))


Option3: Incase you dont have space between name and phone number

Please note that this is an array formula. Within the cell in edit mode (F2)

paste this formula and press Ctrl+Shift+Enter to apply this formula. If

successful in 'Formula Bar' you can notice the curly braces at both ends like

"{=<formula>}"


=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"01234 56789")),LEN(A1))


THIS IS NOT MY WORK, cedit to Jacob Skaria on excelbanter

 

[Luke M]

If you had read this post:

http://chandoo.org/forums/topic/separate-number-from-text-thru-formula#post-27082

You'd see examples using just formulas.


If you read this one, you'll see formulas using UDF's:

http://chandoo.org/forums/topic/separate-number-from-text-thru-formula#post-27087


I'm not sure how much more of an example we can provide.

 

[Satish.digit]

Hi Bob,


You can do it in VBA also below is the code.


Option Explicit


Function GetChars(target As Range)


Dim MyStr As String, i As Integer

MyStr = ""

If Len(target.Value) = 0 Then GoTo GoExit

For i = 1 To Len(target.Value)

If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)

Next i

GoExit:

GetChars = MyStr

End Function


Function GetNums(target As Range)


Dim MyStr As String, i As Integer

MyStr = ""

If Len(target.Value) = 0 Then GoTo GoExit

For i = 1 To Len(target.Value)

If IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)

Next i

GoExit:

GetNums = MyStr

End Function


i assume your values are in A column then you can given the below formula for Alphabets in B Column : =GetChars(A1) and for Numbers :=GetNums(A1)


then you will get the values as you need.


Satish.

 

[bobhc]

Good day Satish.digit


Many thanks, coding in VBA is above my understanding at the moment,...but I will most definitely file your code for future use, my thanks