LEFT, MID, RIGHT!!!!

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

Hi

I have the following data in cells on my spreadsheet

Shuttleworth654125
Price584758
Rogers584523
Tim520212

What I want is to extract both the name and the number to
different cells, the number is fine as it is always the
last 6 digits but the name is causing me some problems,
any ideas?
 
Hi Ed,

for the left part you can use
=LEFT(A1;FIND({1,2,3,4,5,6,7,8,9,0},A1)-1)
Frank
 
Ed said:
Hi

I have the following data in cells on my spreadsheet

Shuttleworth654125
Price584758
Rogers584523
Tim520212

What I want is to extract both the name and the number to
different cells, the number is fine as it is always the
last 6 digits but the name is causing me some problems,
any ideas?
Click to expand...

For data in A1, how about this?
=LEFT(A1,LEN(A1)-6)
 
If Shuttleworth654125 is in A1 do

=MID(A1,1,LEN(A1)-6)

to get name )i.e Shuttleworth.

and of course u already know =right(A1,6) to get the
numbers. Use =Right(a1,6)*1 to convert to number format if
nec.

Hope that helps.
 
Did you try that? It won't work, only if the first numeric value is 1
Paul's formula will work if the numbers always are 6 digits, this will work
as long as the
numbers are to the right

=LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0))

entered with ctrl + shift & enter

obviously to get the numbers just substitute the values from the above
formula with nothing

=--SUBSTITUTE(A1,B1,"")

or the array formula

=--SUBSTITUTE(A1,LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(
A1)))+1,1)),0)),"")
 
Back
Top