To Reformat cells in certain conditions

[Vinu78]

Hi All,

Please help me.
I have the data in Excel in 7 columns and I would like to format it based on mentioned condition

I would like to get formatted as
ZYYY 12345 7896AA TT 823.45
ZEEE 344444 33WEDR TT 12345678.456666
ZEEE 455DF TRGYWEDR TT 2345.456

So it is like Col1 and Col2 should be concatenated without any spaces in between.
We should put Col 3 in right justified with total length of 10 char (so spaces has to be filled on left side;
Then for Col 4, it should also be right justified with total length of 9 char (spaces to be filled on left side);
Then Col 5 should be left justified with total length of 10 char (spaces to be filled on right side of data)
Then column 6 should be concatenated immediately after 10th char
Column 7 is having 9 character before decimal and decimal should be at 10th pos and after that amt after decimal should follow that.

I tried with =CONCATENATE(TRIM(A2)&""&B2) but not sure how to handle Col 3 onwards.

Thanks
Vinu

 

[mamady]

Hi Vinu,

Try the following formula
=CONCATENATE(A2,B2," "&C2&" ",D2,E2," "&F2&" ",G2)

Best Regards
M. Yusuf

 

[Vinu78]

Hi Yusuf,

Thanks for your reply.
Currently I am receiving C2 and D2 as Left justified value.
I would like to format it as C2 which has max of 10 char to be right justified with spaces filled on left side of data (Eg: If the C2 col has data '1234', we should have it as ' 1234' (6 spaces on left side that makes 10 char). I am struck here.

Thanks
Vinu

 

[Vinu78]

Hi All,

Any help in this regard is appreciated

Thanks
Vinu

 

[Joe4]

What are you ultimately trying to do here?
Are you trying to create a text file for exporting?
If so, if you set the column widths to the exact size you need them to be, and then save as a "Formatted text, Space Delimited" file, it should maintain that spacing.

 

[Vinu78]

I am receiving this excel sheet with data from a different team.
So once I receive this excel sheet, I need to format the values as mentioned above and then export it to text file.

Tried below formula
=A2&C2&REPT(" ",10-LEN(D2))&D2&REPT(" ",9-LEN(E2))&E2&F2&REPT(" ",10-LEN(F2))

and it worked perfectly till Amount field which is in G column.
For Amount field, it should be coming as Decimal point at 10th position after Col F and amount before and after decimals should be populated accordingly. I am completely struck at this point

Regards
Vinu

 

[Joe4]

See if this addition for column G works for you:
...&REPT(" ",9-LEN(INT(G2)))&G2

 

[Vinu78]

Thanks very much Joe.
Your formula works great.
...&REPT(" ",9-LEN(INT(G2)))&G2

Is it possible to fill in ZEROES after decimal part instead of spaces. Eg: 823.45 to be stored as 823.450000

 

[Joe4]

Assuming that you always want 6 decimal places, try tacking this on to the end of what you currently have:
...&IFERROR(REPT("0",6-(LEN(G2)-FIND(".",G2))),"000000")

 

[Vinu78]

Thanks a lot Joe. That worked perfectly.