 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Need to do some lookups.
Unfortunately some numpties have decided to represent the same data differently.
AB0123
AB123
Any quick and easy way I can delete the zero or indeed introduce the zero?
Thanks
If it's always the first 2 characters then 3 or 4 numbers you can use something like
LEFT(A1,2)&TEXT(MID(A1,3,99),"0000")
just use 3 0's at the end of the TEXT functon if you want to get rid of the first 0.
if you just want to get rid of the 0 then the easiest way would be to use the find and replace.
Highlight the cells, then Find 0, replace with <blank>
if you just want to get rid of the 0 then the easiest way would be to use the find and replace.Highlight the cells, then Find 0, replace with <blank>
Although quick and easy you'll also catch any other 10s 20s etc with that.
If you're going to do a hacky find and replace then do something a little more creative like find AB0 and replace with AB.
this of course assumes there's not any ACs BAs etc
As above it depends if all data matches the format in your examples
If so i'd use 'LEFT' 'RIGHT' and 'CONCATENATE' to pull out the bits I wanted into a new column.
Depending on the mess its in you could use Text to Columns to separate out the numbers. Then manipulate as required and Concatenate to merge the results.
if it'll always be 3 numbers i.e. 123 and not 1123, then use this in the adjacent column and copy down the whole list
=IF(LEN(A2=5),LEFT(A2,2)&"0"&RIGHT(A2,3),A2)
this will either copy the adjacent cell if its AB0123 or add in the 0 if its AB123 to give a 6 character result