Hi,
I'm trying to write a LEFT formula in Excel that returns the 1st 3 numbers from a 12 digit field, however if the source number begins with a "0", then LEFT,3 just gives me the 2nd, 3rd and 4th digits instead. The number i'm looking up is 075-23459012, i want the result to be 075, so that i can build an IF solution in another workbook.
How can i improve my formula to include the 0 at the start if at all?
Thanks
if its not returning the leading zero then the cell must contain a number custom formatted 000-00000000
so try =LEFT(TEXT(A1,"000-00000000"),3)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
A1 cell
075-23459012
B1 cell
="'"&LEFT(A1,3)
Change the cell reference A1 to your cell
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
=LEFT(A1,FIND("-",A1,1)-1)
this formula results "075" and its format is text, if it will be formatted as number 0 number will be deleted
Thats great guys, bit of variation, but all working fine now. Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks