Help!
I have a cell that contains numbers and text.
example: 1-2-3-4-5-6 or 1-22-3-44-5-6.
If the the number between the dash '-' is a single digit (less than 9), I want to insert a leading zero so all 6 numbers are 2 digits.
Is this possible with custom formatting? I tried a custom for of:
00"-"00"-"00"-"00"-"00"-"00
and it is not working.
I also tried using a MID formula but it gets quite complex identifying the location of the dash - is there an easier way than the MID or LEN?
Please help!
Hi,Originally Posted by Special-T
easiest would be to insert 6 columns following the numbers, then select the numbers column and do Data, Text to Columns, Delimited, Other - (dash)
then join them together with
=TEXT(A1,"00")&"-"&TEXT(B1,"00")&"-"&TEXT(C1,"00")&"-"&TEXT(D1,"00")&"-"&TEXT(E1,"00")&"-"&TEXT(F1,"00")
After this copy the new column, the Paste Special = Values and delete the helper columns.
hth
---
Si fractum non sit, noli id reficere.
Thanks, but I'm downloading the number of our web site and it is one cell without the leading zeros. 1-8-16, etc.
Ultimately I want to have each number broken out into separate columns so I can perform other calculations on the components of the number.
I don't understand how using the Text function will work given that the orignal cell data is a single cell with text and numbers? any other ideas?
thanks,
If I understand you correctly, you download your numbers ie.
1-8-2016
1-2-3-4-5-6
1-22-3-44-5-6
But you want to put each group of numbers between the "-" into two numbers, but if it's only a single number add a leading zero?
If that is the case, then select the cells, go to Data > Text to columns, Step 1, choose Delimited > Next, Step 2, choose Tab and Other and put in "-" (without " ") > Finish.
Then select all the cells, right click Format > Number > Custom, replace General (in the Type: box) with 00. All the single numbers should now have a leading zero. Now with all the cells selected, right-click, Copy > Paste Special, Values.
oldchippy
-------------
Blessed are those who can give without remembering and take without forgetting
If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.
Click here >>> Top Excel links for beginners to Experts
Forum Rules >>>Please don't forget to read these
The number I receive from the our wed already has the dashes. I want each number between the dashes to be 2 digits, so if the number is less than 10, I want it to add a leading zero.
example:
1-2-3-5-6, I need it to be 2 digits example: 01-02-03-04-05-06.
I will not receive a number that is ever greater than '45' so I will never have a situation where there is 3 digits between the dashes.
thanks for your help!
Then you need to do exactly what Bryan has suggested, that will do it
I don't understand how to do that and still add the leading zero? I know I must be missing something????
OK, just follow this slightly (maybe) easier approach
Select your column with the down-loaded numbers, then go to Data, Text to Columns, Step 1, choose Delimited, click Next. Step 2 choose Tab and Other and put in a - (dash). Click Finish. This has now split your numbers into 6 columns.
Now to join them together with a dash in between the groups use this formula in G1
=TEXT(A1,"00")&"-"&TEXT(B1,"00")&"-"&TEXT(C1,"00")&"-"&TEXT(D1,"00")&"-"&TEXT(E1,"00")&"-"&TEXT(F1,"00") auto-fill the formula down the column.
Result should be this 01-02-03-04-05-06 or 34-23-01-09-15 depending on the original numbers.
Now to make the formula's into static values, select them all, right click, Copy > Paste Special > Values.
Does this help?
OK - it worked! I feel really stupid right now! thanks so much for your help i have been struggling with this for days!
Glad you got your result - thanks to Bryan as well (he's probably tucked up in bed right now)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks