Extract text or numbers

[Nikhil]

I have data in celss in the format

A665BT
62ABC
ABC62

I want to segregate numbers and caharcters.

Any help?

 

[Cloverken]

Hi Nikhil,

ASAP has an easy utility that can help. You can download at

http://www.asap-utilities.com

Once ASAP utilitiy is installed. I would copy column with data so that you would then have two identical columns with text and numbers mixed.

Then select first column and go Excel menu and select "ASAP Utilities" then "Text" then "Delete all text characters in selection" then "OK".

Select second column and go to the Excel menu and select "ASAP Utilites" then "Text" then "Delete all numbers in selection" then "OK"

That would separate numbers from text in two separate columns

 

[timwangTM]

Also try following. Select the cell you want to start to separate, then run the codes.
===============================
Sub Separeate()
Dim i As Integer
Dim x1, x2
Dim Number As Long
Dim Text As String
Dim myCell As Range

Set myCell = ActiveCell
On Error Resume Next
Do Until myCell = ""
For i = 1 To Len(myCell)
x1 = Mid(myCell, i, 1)
x2 = CInt(Mid(myCell, i, 1))
If Err Then
Text = Text & x1
Err.Clear
Else
Number = Number & x2
End If
Next
With myCell
.Offset(0, 1) = Number
.Offset(0, 2) = Text
Set myCell = .Offset(1, 0)
End With
Text = ""
Number = 0
Loop
End Sub
=====================================
[/code]

 

[PaddyD]

given the examples you posted, what are the outputs you need?

 

[fairwinds]

Hi,

I think these formulas should cover the different strings you have.

The one for numbers must be expanded if your strings ar longer than 20 char's.

Book1
	A	B	C	D	E	F
1			Numbers	Text		Intermediate
2	aaa123		123	aaa		aaa
3	123aaa		123	aaa		aaa
4	1a2s2d3d		1223	asdd		asdd
5	545eee978		545978	eee		eee978
6	12312		12312
7	abcde		0	abcde		abcde
8	aaaasd456234		456234	aaaasd		aaaasd
Sheet3