I have part numbers that have been entered as 8V0473650000 and need to show as 8V0-47365-00-00 There can be multiple letters in the part number in different location ie 8CH1249A0300 needs to show as 8CH-1249A-03-00 Excel has no problem doing this if its just a number but when you thow in some letters no go!
How do you format a cell in Excel to show numbers and letters?microsoft windows xp
Use TEXT format. This will let you type letters and digits, and display them exactly the way you typed them.
How do you format a cell in Excel to show numbers and letters?ie 7 internet explorer
right click and go to format cells... under the tab "number" there is a list, choose general
you want to put a little quotation mark before your entry. That way excel knows that you wish to enter it exactly as shown.
'
if all your numbers are already entered as 1 long string like your sample, then just leave it the way it is.
lets say you have 8CH1249A0300 in A1
then in B1 i would use this formula
=CONCATENATE(LEFT(A1,3),"-",
MID(A1,4,5),"-",
MID(A1,9,2),"-",RIGHT(A1,2))
copy and paste this formula together in cell B1 (so its 1 long formula) i had to break it up so yahoo will show it all
then copy and paste down your 3000 rows
then when its done,
-copy your B column
-right click on a cell
-click paste special...
-click values
and ok
edit-
oh yeah, consistency is important. if the whole column is 12 digits, this is fastest.
if someone took the time to enter some of the cells with the dashes (making the column inconsistent) then do a find %26amp; Replace (Ctrl-H) and get rid of the dashes
-highlight your column of parts
-click Edit
-click Replace...
-in find what box, type -
- leave the replace with box empty
-then replace all
hope this helps
just do what you had done before, except use ? instead of #
???-?????-??-?? is what it shoud look like :)
Cheers
No comments:
Post a Comment