LinkBack URL
About LinkBacks
hi i have a column filled with 13 digit numbers and i am looking to remove 5 digits from that number e.g 5020073417357 i need to remove 41735 (8 to 12) from every one in the column.
hi i have a column filled with 13 digit numbers and i am looking to remove 5 digits from that number e.g 5020073417357 i need to remove 41735 (8 to 12) from every one in the column.
Last edited by andywil; 02-20-2013 at 01:20 PM.
Assuming that's text rather than a number, this should work:
=LEFT(A2,8)&RIGHT(A2,1)
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Also,
=REPLACE(A2,9,5,"")
Regards,
Haseeb Avarakkan
__________________________________
"Feedback is the breakfast of champions"
@Haseeb: I did not know that. Thank you. Much neater.
Regards, TMS
thanks for the help guys but it is not leaving the numbers i need e.g 5020073356564 in need 35656 from this dont need the first 7 5020073 or the last number 4 only need 35656 from it thanks
Hi andywil
Assuming your data is in column A, A1. in B1:
Formula:
Please Login or Register to view this content.
Last edited by Kevin UK; 02-20-2013 at 01:32 PM.
Regards Kevin
Merged Cells (They are the work of the devil!!!)
Try this, it returns a number, not a string.
Formula:Please Login or Register to view this content.
Last edited by Marcol; 02-20-2013 at 02:12 PM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
@ Marcol
Yes forgot that!
Updated:
Formula:Please Login or Register to view this content.
thanks guys worked a treat now how can insert the formula so it will do this for a column of 20 rows removing the 5 digits i require for each 13 digit number in the rows
Hi andywil
Assuming Data is in A1:A20, put the formula in say B1 and copy down.
thanks for all the help guys great forum
Last edited by andywil; 02-20-2013 at 03:47 PM.
can i replace the 13 digit number from the rows with the extracted 5 digit number into the original rows
Hi andywil
Copy the column/cells with the extracted values, select the column/cells of the original data, paste special, paste values.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks