Custom Number Formatting help

[GoodguyAB]

Hello

Pretty new with excel! using youtube and forums to get stuff done. Hoping someone could help me out with this one

1. I am working with LSD locations and the format required is 00-00-000-00W0
Most of them are in the format of 1-11-1-1W5
output should be 01-11-001-01W5
I came up with "0"#"-"##"-""0"##"-""0"#"W"#

The problem I am having is the data was entered without the zeroes and sometimes I have LSDs that are 11-11-010-1W

So I have to manually go and change the number formatting as the LSD above does not require the zeroes.

Is there a better way of approaching this? I feel there is a better way to do this but I just don't know enough yet.

Thank you!

[Keebellah]

If you se the number format to

Please Login or Register  to view this content.

and enter 111001015 in that cell the result will be 01-11-001-0W15

[MrShorty]

What will the actual cell entry/values typically be? 6 digit integers? variable digit integers? text?

[GoodguyAB]

The cell usually has text and the LSD in it, so I am extracting the LSD from it using different right/mid/left/len type of formulas. I am not typing in the data, as the data it is already there. It is just raw data so it is in the incorrect format and with extra information that we don't need for the purposes of this project. thank you!

[GoodguyAB]

If you se the number format to

Please Login or Register  to view this content.

and enter 111001015 in that cell the result will be 01-11-001-0W15

Thanks for the reply! I am not entering the data though - the data it is already entered as 1-11-1-1W5. I have about 5000 entries so I was trying to avoid typing the data again.

[MrShorty]

Number formatting does not format text strings. I think this will need to be done using text manipulation operations/functions. Not sure exactly what help you will need. I would probably:

1) A Text to columns operation (or function equivalent like Google Sheets SPLIT() function) that separates each section into separate numbers.
2) Assuming the W is a constant in the last piece of text, split that last piece around the W.
3) Concatenate the 5 different pieces back together (separated by "-" and the last "W"), using the TEXT() function (https://support.office.com/en-us/art...8-93d29371225c ) so that each part has the correct number of digits (something like TEXT(3rd number,"000") will make sure the 3rd part has 3 digits). This will probably look something like =CONCATENATE(TEXT(first part,"00"),"-",TEXT(second part,"00"),"-",and so on)
4) Note that the final result is a text string, not a number.

I have not worked out a detailed sequence of formulas/operations, because I don't know exactly what you would need help with (and many of these guys are better at text manipulation functions than I am). That's the overall process I would expect to use. If this is something I was going to be doing frequently, I might be tempted to write a VBA UDF to do this. If this is a one time or infrequent task, I would probably work through the process manually.

Has anyone else got some more specific ideas? How do you want to proceed?

[GoodguyAB]

Number formatting does not format text strings. I think this will need to be done using text manipulation operations/functions. Not sure exactly what help you will need. I would probably:

1) A Text to columns operation (or function equivalent like Google Sheets SPLIT() function) that separates each section into separate numbers.
2) Assuming the W is a constant in the last piece of text, split that last piece around the W.
3) Concatenate the 5 different pieces back together (separated by "-" and the last "W"), using the TEXT() function so that each part has the correct number of digits (something like TEXT(3rd number,"000") will make sure the 3rd part has 3 digits). This will probably look something like =CONCATENATE(TEXT(first part,"00"),"-",TEXT(second part,"00"),"-",and so on)
4) Note that the final result is a text string, not a number.

I have not worked out a detailed sequence of formulas/operations, because I don't know exactly what you would need help with (and many of these guys are better at text manipulation functions than I am). That's the overall process I would expect to use. If this is something I was going to be doing frequently, I might be tempted to write a VBA UDF to do this. If this is a one time or infrequent task, I would probably work through the process manually.

Has anyone else got some more specific ideas? How do you want to proceed?

Thank you for the reply, I am going to look into the TEXT function
This is a one time thing, and the biggest challenge is to extract just the LSD data from the cell and then have the output to be in the right LSD format. I am going to explore some more. Thanks again!

[Keebellah]

Can you attach a file with some raw data?
Makes it easier than assuming