Log in

View Full Version : Breaking numbers and letters



kurt0101
06-26-2008, 08:00 AM
Hi I have a simple question but I can not seem to find the answer online.
I have a cell that contains
a four digit number immediately followed by a letter and all I want to do is break this up into four digit number and letter into TWO cells

example:
input
1000C
output
1000 C (the space represents adjacent cell)

also i would like to be able to do this if i have a letter followed by a four digit number
example
input:
N1995
output:
N 1995


thank you very much

mdmackillop
06-26-2008, 08:06 AM
=Left(A1,4) and =Right(A1,2)
You could test the first character by using Code(A1) and an If statement to do this automatically.

kurt0101
06-26-2008, 08:15 AM
thank you very much. that seems to work very well.
if i need further assistance i will contact you. thank you again

kurt0101
06-26-2008, 10:00 PM
Hi I have encountered an unexpected problem.
Sometimes by data has 3 digits followed by a letter and sometimes it has 4 digits followed by a letter. It occurs randomly. Is there a solution to this?

thank you

Bob Phillips
06-27-2008, 12:39 AM
=SUBSTITUTE(A1,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))),"")&" "&
LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

marshybid
06-27-2008, 01:00 AM
=SUBSTITUTE(A1,LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))),"")&" "&
LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))
Hi xld,

Just used the formula above, it will prove very useful for some of the data I am dealing with (slight modification needed only).

However, this formula splits the values, places the Alpha character at front, then space then Numeric into one cell.

How could this be modified to show the Alpha Character in Col B and the Numeric in Col C, or vice versa?

Also, despite looking at some of the sites recommended, I still don't understand the use of -- ???

Thanks,

Marshybid :hi:

Bob Phillips
06-27-2008, 01:11 AM
B1: =SUBSTITUTE(A1,C1,"")

C1: =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW(INDIRECT("1:"&LEN(A1))))))

Cosmo
06-27-2008, 05:38 AM
Hi I have encountered an unexpected problem.
Sometimes by data has 3 digits followed by a letter and sometimes it has 4 digits followed by a letter. It occurs randomly. Is there a solution to this?

thank you
Is it always a single letter at the end? If so, you can use the following

Dim theString As String
theString = "1234A"
Dim digits As String
Dim letters As String

If Not theString = "" Then
letters = Right(theString, 1)
digits = Left(theString, Len(theString) - 1)
End If

mdmackillop
06-27-2008, 12:58 PM
Also, despite looking at some of the sites recommended, I still don't understand the use of -- ???


-- forces a True or False value to become numerical 1, 0 or -1 as appropriate.

Bob Phillips
06-27-2008, 01:57 PM
I thought I had posted this to help explain the double unary.

In A2 enter 1234A

Then in B2 enter =ISNUMBER(LEFT(A2,4))

And finally, in C2, enter =ISNUMBER(--LEFT(A2,4))