Formatting data from TRIM function

[jimonfly]

I am using the TRIM function to pull information from one sheet into a pre-designed table. I want the new table to contain the data in the same format as the original regarding 1,000 separator comma and % (pulls in .20 instead of 20% from original). Also, I want blank cells to be blank, and not contain "0". The original data is formatted correctly and contains a mix of text, dates, numbers and percentages.

Is there another function to use?

 

[Norie]

How are you using TRIM exactly?

 

[jimonfly]

My formula is: =TRIM('Lease Expiration Excel'!D16)

I have data exported from another program into an excel sheet. I copy/paste the data from that sheet into a sheet in my workbook. What I want to happen is the data will be pulled from that sheet into a predefined table on another sheet. My problem is the pasted sheet is formatted for text, number, percentage already. After I use the TRIM command, the formatting is defaulted back to General, and I loose my 1,000 separator comma and my % formatting. If I don't use TRIM, I get "0" where blank cells should be.

After the data is brought in to the predefined table, I can change the cell formatting off the right-click menu, but the changes have no affect on the data (no 1,000 commas, % signs).

 

[shg]

TRIM returns a string, so the number formatting doesn't apply.

How about =if(d16="", "", d16)

 

[jimonfly]

Thanks, that will work. I can manually format the percentage row afterward because I cannot predict where it will be located.

Jim