# MS Excel Help Please

Afternoon All,

I'm someone can help me with an Excel problem I have.

Basically I'm trying to seperate a column to split text and numbers.

For Example in the columns i have values such as B1, BL23 etc...and what i want to do is split them so in the Column it will show B, BL etc and then others will show the numbers

Can anyone help.

Thanks

Abz

I'm someone can help me with an Excel problem I have.

Basically I'm trying to seperate a column to split text and numbers.

For Example in the columns i have values such as B1, BL23 etc...and what i want to do is split them so in the Column it will show B, BL etc and then others will show the numbers

Can anyone help.

Thanks

Abz

Too obvious I guess so assume I have misunderstood.

Banned

theworldofoffice.com/exc…mn/

copy and paste your data into a second column. Replace each number in turn 1-0 in one column and then relace each letter in turn A-Z in the other column.

Not the quickest solution but sometimes you can spend more time trying to find a formulae or macro than you can doing the task manually

[code]Function NumberOut(rng As Range, Text¦Number)

Dim i As Integer

If Text¦Number = 0 Then

For i = 1 To Len(rng)

Select Case Asc(Mid(rng.Value, i, 1))

Case 0 To 64, 123 To 197

Case Else

NumberOut = NumberOut & Mid(rng.Value, i, 1)

End Select

Next i

Else

If Text¦Number = 1 Then

For i = 1 To Len(rng)

Select Case Asc(Mid(rng.Value, i, 1))

Case 0 To 47, 58 To 197

Case Else

NumberOut = NumberOut & Mid(rng.Value, i, 1)

End Select

Next i

Else

NumberOut = rng.Value

End If

End If

If NumberOut = 0 Then NumberOut = " "

End Function[/code]

Edited by:"trog0" 16th Nov 2010[ hotukdeals.com/mis…916 ]

BFN,

fp.

Ooh just seen that one. a simpler solution to that would be

this would allow for expandability without making the calculation too complicated. The repetition of the IF statment is just to cover for people deleting cell contents by pressing space

Edited by:"trog0" 16th Nov 2010Original Poster

and yes you did address the issue from 4 months ago!

on a side not i've had an excel add on called ASAP utilities which is amazing for doing things like i asked for on this thread! Worth looking into if you use excel quite a bit!