• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Numbers to Text - High Volume

polarisking

polarisking

Member
I have a column of member Ids - 6 digits, many with leading zeros - ~7,000 of them. I get other files with information that I attach via vlookups using this member number.


Each time a new file comes in, I have create a new column formatted as Text, strip off the spaces from the original value, format as 000000, and copy back into the original column as text.


Is there a more straight-forward way to do this?
 
instead of formatting the column as text, use can use a custom format. go to format cells by right clicking, choose custom. In the field called Type: put in 000000. that should do the trick.
 
Since your lookup table is in text, sounds like you're stuck with that part, but your helper column could just be:

=TEXT(A2,"000000")


This would convert the value to text and put the correct number of leading zeros.
 
Hi Polarisking,


Suppose the file coming in has got all the member ids in Column A, starting from Cell A2, along with other information in other columns. We need to get rid of spaces from member ids and format these to match with your master file's format for member ids, i.e. "0000000" - I hope this is what you need to do, right?


Select Column B, insert a new column and in newly inserted column at the cell B2 enter:

=TEXT(Trim(A2),"0000000"), and fill this all the way down! You can do the look-up based on this newly made column as well!


Hope this helps!


Regards,
 
You must log in or register to reply here.
Back
Top