Found 15th Nov 2010
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

7 Comments

Is the answer in the question - ie, actually split the column into 2 and put letters in one and number in the other with a blank border in between?

Too obvious I guess so assume I have misunderstood.

A non techy way of achieving this would be to use the find and replace function Ctrl+F. and replace with nothing

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

This slight modification to allexperts.coms vba program allows you to select betweeen filtering out text (1) or Numbers(0) the assumption here is that you have mixed data such as b1f113f1 or 1ba11. if the assumption is wrong and you always have letters as a prefix with numbers after it then it shouldn't be too hard to change the way it works.

[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

Did we address your query from 4 months ago, Abz?

[ hotukdeals.com/mis…916 ]

BFN,

fp.

fanpages

Did we address your query from 4 months ago, … Did we address your query from 4 months ago, Abz?[http://www.hotukdeals.com/misc/ms-excel-help-please/714916 ]BFN,fp.



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

=IF(COUNTIF(insert range here,""),"Inactive",IF(COUNTIF(insert range … =IF(COUNTIF(insert range here,""),"Inactive",IF(COUNTIF(insert range here," "),"Inactive","Active")


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 2010

Original Poster

Thanks guys for your help....

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!
Post a comment
Avatar
@
    Text
    Top Discussions
    1. Topcashback Trick or Treat competition56529
    2. Free money and competition entry @ Circle56
    3. Free flights to Europe712
    4. Free Soap & Glory Bath Bomb (High Score)44

    See more discussions