Breaking numbers and letters

[kurt0101]

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]

=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]

thank you very much. that seems to work very well.
if i need further assistance i will contact you. thank you again

[kurt0101]

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]

=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]

=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]

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]

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]

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]

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))