Format text issue

[punter]

Hello everyone. The Thank you in advance for you help.

The attached workbook contains my seemingly simple problem.

I have data that needs to be formatted in a certain way in order to upload it into a finacial system. It needs to be in text (column B). The problem I'm running into is that I can't seem to format it or get a formula that will work that will return the values in Column B. The only way I can get that is to type it in manually. With thousands of lines per entry this is not an option.

Does anyone know a formula or format trick that I could use? This seems so simple but I can't find the answer.

Thanks

Eddie

[Richard Buttrey]

Hello Eddie,

I don't think we have enough information. How do you get from 5317 to 05317? i.e. What are the rules we must ask Excel to use. e.g.
Do we always add a leading zero?
What if the number is less than 4 digits, e.g. 317. Do we add two zeros to get 00317?
What about more than 4 digits? Do we still add a zero?

Give us the rules and we'll no doubt come up with an answer.

Rgds

Rgds

[punter]

Forgive me. I should have laid it out.

It is in two parts:


1-The number values (as on the attached sheet) will always be five digits. It is store 2 I need four zeros added. If it is store 5317 I will need one zero added. They will all have to be five digits and in the format of column B.

2-There will be non numberic values but I guess they would be easier. Example: I would need New York in the same format (no zeros of course) of column B on the attached sheet. The system needs to pull everything in the same way.

[ChemistB]

Are these calculated values? If so
=TEXT(your formula,"00000")

If not, in another cell
=TEXT(A2,"00000") dragged down.
Is that what you are looking for? If you are exported the data though, you'll probably need to copy>paste special>values over top of the formula to finalize it. They will stay text.

[punter]

Thank you so much!!!!!

That works wonderful for the numeric values.

What would I do if I wanted to get the same format for a non numeric value (without the zeros of course)?

[ChemistB]

Not sure what you are asking..... If it's non-numeric, then above formulas will just keep it the same (i.e. if "New York" is in A2.