Hi,
I am using excel 2010 and I can visually see the leading zeroes and dash (-) but when I click on perticular cell leading zeroes and dash are disappearing.
For example:
Visually I cam see : 02842-4673
But when I click on that cell I can see just 28424673
Can I get any help on this.?
Thanks!
Sounds like you have the column or cells 'Formatted' to be Special> Zip Code + 4. What this means is you visually see a format to a string that is in there, in your case 9 consecutive numbers.
Do you want a certain range out of that...will give you the first 5 and
Please Login or Register to view this content.will give you the right 4. If you actually want the dash to be in there, you can do a combination of those asPlease Login or Register to view this content.
Be sure there are absolutely no hyphens in place so that you do not duplicate on some that happen to actually have it. Do that by doing a find and replace of all hyphens in that range with nothing.Please Login or Register to view this content.
EDIT: Of course CELL = the cell reference such as A1, where ever the zip codes live on your sheet, and then apply down the column.
-If you think you are done, Start over - ELeGault
Hi,
What help do you need? The cell contains the number and is formatted to show what you see. That's how formatting works. If you want the cell to CONTAIN 02482-4673 then you'll need to enter it as text not a number.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi,
I have a spread sheet that I will share.Can it possible to show how I can covert it as text and see the actual data without loosing dash and leading zero?
Thanks!
Hi,
Have you tried the solution EleGault offered.
i.e. in B2 copied down
Formula:
Please Login or Register to view this content.
If you're expecting to have both the number and the text in the same A2 cell then you can't.
Purely as a matter of interest why do you want to change anything? You already see what you want to see. Is it that you're wanting to copy the text value somewhere else?
Hi,
If I use this formula =LEFT(A2,5)&"-"&RIGHT(A2,4)
The value of '48091' this becomes '48091-8091'
And we need to convert the data as text for some importing purpose.
Any macro solutions?
Thanks!
Your original question was about an 8 digit number. You now appear to be talking about a 5 digit number.
You need to tell us all combinations/permutations of your numbers/length if we are to give you a solution.
Whether that is a macro or a function is irrelevant at this stage until we understand your data and the ways in which you want to manipulate it.
Hi,
I have already attached the spreadshhet.
The objective is to append leading zeros to make the zipcode 5 character length if the length is more than 3 and less than 5 chharacters and convert the entire zip code column into text field.
It would be 3 to 9 characters.
Thanks!
Maybe in B2
Formula:Please Login or Register to view this content.
try
=TEXT(--SUBSTITUTE(A2,"-",""),"00000-0000[>99999];00000[<=99999]")
"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
Hi,
I have worked on this fomula but if you see this data,actually original zip code is '02777-3239' and after run the formula it is loosing leading zero and dash is moving to next position '27773-3239'
Can it possible to modiy?
Thank You!
see post #10
02777-3239
or 27773239
is shown
as
02777-3239
Hi But I do not want to add leading zeroes if the value is less than 3 that is 12,or just 1.
The above '=TEXT(--SUBSTITUTE(A2,"-",""),"00000-0000[>99999];00000[<=99999]") ' formula is adding 4 leading zeroes if the value is one character length.
Thanks!
examples? of said zips
If the zip code is just 12 it is adding 00012 and you can say also if the zip is jut 4 it is adding 00004 like that.
Thanks!
are there such zip codes? what is a real example address
Hi ,
Actually I need to highlight that one or two character zip codes and remove it manually later so I do not want to add leading zeros to these invalid zip codes.Idealy if you can highlight these type of codes instead of adding leading zeros would be the perfect solution.
Thanks!
=IF(LEN(TEXT(A2,"0"))<3,"error",TEXT(--SUBSTITUTE(A2,"-",""),"00000-0000[>99999];00000[<=99999]"))
change len()= size to suit
Hi Instead of putting error possible to highlight those cells in colour?
Thanks
just conditional format =len(a1)<3
Hi,
May be a little UDF
Refer the attachment...
Please Login or Register to view this content.
☚ Click ★ just below left if it helps, Boo?ath?
Hi,
Can you help me what you have put in the attached sheet and how I can run this?
Thanks,
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks