MS Excel Help Please - HotUKDeals
We use cookie files to improve site functionality and personalisation. By continuing to use HotUKDeals, you accept our cookie and privacy policy.
Get the HotUKDeals app free at Google Play

Search Error

An error occurred when searching, please try again!

Login / Sign UpSubmit

MS Excel Help Please

£0.00 @
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 a… Read More
Abz Avatar
6y, 8m agoPosted 6 years, 8 months ago
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
Abz Avatar
6y, 8m agoPosted 6 years, 8 months ago
Options

All Comments

(7) Jump to unreadPost a comment
Comments/page:
#1
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.
#3
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
#4
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 on Nov 16, 2010 00:15
#5
Did we address your query from 4 months ago, Abz?

[ http://www.hotukdeals.com/misc/ms-excel-help-please/714916 ]

BFN,

fp.
#6
fanpages
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 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 on Nov 16, 2010 05:08: grammar
1 Like #7
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

You don't need an account to leave a comment. Just enter your email address. We'll keep it private.

...OR log in with your social account

...OR comment using your social account

Thanks for your comment! Keep it up!
We just need to have a quick look and it will be live soon.
The community is happy to hear your opinion! Keep contributing!