Text to Column

[pranab79in]

I have to consolidate data from multiple columns and make it flow to a single column so that I can use to convert to .txt file, I have used Concatenate formula to do so and now I have this value 0.30 CA in C6 of my excel sheet, however I do not want the "0" in the decimal to flow and want .30 CA to flow to D6.

Looks very silly but I am unable to do this. Any help on this will be appreciated.

[queuesef]

if you put all the data in columns in Excel you can then save the file as TYPE CSV ~ which will put in the COMMAs to separate the data. You can open the file using Notepad....if needed you can use SEARCH and REPLACE (in Notepad: from the menu bar EDIT, REPLACE (where you would type "," in the FIND and " "(a blank space in the REPLACE.

If you send an example, I could better assist you if this is still an issue?

[pranab79in]

Thank you for your quick response, Please see attached I am struggling to remove "Zero" from C6, I need .30 CA in D6 and in that range. Let me describe this further, I have Pre-populated data in A6 and data coming from another sheet in B6 which have concatenated in C6 however I need the zero to be removed from this. I am unable to remove it using Text to Columns as it shows up the formula. Is there a work around. Thank you once again.[/ATTACH]

[samba_ravi]

="."&mod(a6,1)*10^2&b6

[queuesef]

I like the solution from nflsales. Depending on the data, if all the numbers are in the same column, you could format them without the leading zero...see format Cells the CUSTOM.