Excel 2010 Formula and Formatting question 3

[ITALIAORIANA]

Hi,

I have a spreadsheet with values in column A. Not all cells have data. The previous data needs to copied down. I inserted a column and created a formula to get the values copied down as there are over 42,000 rows with intermittent missing data. I wrote a formula =IF(ISBLANK(A2),B2,A2) to fill in the missing data. I wanted it to return all the data from column A so it would be a quick copy and paste and not have to copy and paste only the missing data as that would take forever with all the rows.

The problem is, the second value is 00000 and the formula returns "0". Also value 00100 returns as "100" , value 00102 returns as "102" and so on.
Once the formula is completed the results will be pasted into column A and then uploaded to a database. I can't figure out how to get the formatting as text so the formula will keep the leading 0's.
Any help will be greatly appreciated.

COLUMN A
0007T
00000


0009F
0009T
0010F
0010T
00100
00102
00103
00104
0011F
0012F
0012T
00120
00124
00126
0013T
0014F
0014T
00140
00142
00144
00145








00170
00172
00174
00176
0018T
0019T
00190
00192
0020T

Thanks
Deana

 

[jonsi]

firstly, to get 00102 to read as 00102 and not 102 format your cell
Highlight the cells to be formatted
Right Click, choose Format Cells...
Select Custom
In the Type box key in 00000 (5 zeros)
click OK

Set a Filter to allow you to show/not show the blank cells
Select the column
click on filter
keep Select all checked just remove the tick alongside Blanks
et voila! all the column shown except those where there is a blank

hwyl
Jonsi B-)
"If an apple a day keeps the Doctor away ...why don't Daleks live in Orchards?"

 

[ITALIAORIANA]

THANK YOU SO MUCH jonsi!!! I was choosing "text" to format and it wasn't getting me the results I needed. It honestly hadn't occurred to me to try a custom format. This worked perfectly.

 

[zelgar]

You may wish to convert the numbers to a text since everything else in that column is text (e.g., 020T). If you don't convert the numbers to text is that if you're doing anything with the data, it might not work properly since you have a combination of number and text in the column.

 

[SkipVought]

Changing the FORMAT of the number actually changes NOTHING. The underlying value is still 102! Only the DISPLAY (what you see) changes. Your value remains 102.

It really depends on what you really need in the cell. If the value you really need is 00102, then you need to change the underlying value, 102 to 00102, which is TEXT and not a number, via the TEXT() function.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ITALIAORIANA]

Hi Skip and Zelgar,

Yep, I was having issues uploading the data to the table. It kept reading the value as 102 and not 00102 when I uploaded. I inserted a column and used =TEXT(A2,"00000") then pasted those back in and the value is now 00102. I am no longer having trouble uploading the excel file.

Thanks for that. It wasn't working out for me in the database until I used the Text.

Thanks
Deana