Copying decimal values to different cells with leading/trailing zeros

I am having a bit of difficulty with a spreadsheet.

I have 4 columns (and a couple of other text columns) that are formatted as decimal with 6 dicimal points.

I need to create a text file with lines that are 80 bytes each from the spreadsheet - no spaces inbetween each of the characters.

The issue I am having is trying to format the 4 decimal point columns to be fixed numbers and retain the leading or trailing zeros to do a concatenation with the other columns.

Just an example of some what some of the values look like on my spreadsheet:

.123456 | .123450 | .012345 | .012340

Essentially when I'm all said and done, I would like the row to look like "text123456123450012345012340text"

I tried to format 4 new columns as general and use a "=right(cell,6)" and that worked pretty well for the cells that have leading zeros, but for the cells that have trailing zeros, it doesn't seem to pick up the zero. I tried to use a "=mid(cell,2,6)" but that didn't work either.

Any ideas?

Thanks

The decimal point would be treated as a character - do you want this missed off the string? If so you can do this:

="start text"&TEXT(A1*1000000,"000000")&TEXT(B1*1000000,"000000")&TEXT(C1*1000000,"000000")&TEXT(D1*1000000,"000000")&"other text"

assuming your numbers are in the cells A1 to D1.

Hope this helps.

Pete

Beautiful!!!

Worked very nicely. Thanks for reading in that I wanted to get rid of the decimal, because that is exactly what I needed to do.

Initially I was trying to do each column separately with the =a1*1000000 and then once I had all of the columns formatted, I was doing the concatenation, but then I noticed I was loosing the leading zeros, so I was trying to do other things.

Thanks again.

Roger

You're welcome, Roger - thanks for feeding back.

Please mark the thread as Solved if you think it is - click on Thread Tools just above your first post.

Also, you can pass on thanks more directly by clicking on the "star" icon in the bottom left corner of any post that has helped you (not just on this thread).

Pete

my sugestion, format them all as text which will preserve the number with leading and trailing zeros, since it will be converted to a text doc, you won't care if the numbers are formatted as text