# To Reformat cells in certain conditions

#### Vinu78

##### New Member
Hi All,

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

You are welcome.

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### Vinu78

##### New Member
Joe,

I have tried different data and only 2 scenarios didn't worked properly.
If the Amount comes without any value before decimal like .75, the decimal comes at the 8th position instead of 9th position.
Also if the amount comes without decimal, can we force the decimal at 9th pos and display as 123.000000

#### Vinu78

##### New Member
Ho Joe,

I have tried with different data options and except 2 scenarios everything worked properly.
If the Amount comes without any value before decimal places like .75, the decimal point didn't come at the 10th pos instead it came at the 9th position. Eg: ' .750000'
Also if the amount comes without any decimal value that is 123, the value is populated as '123000000' without any decimal at 10th pos.

Regards
Vinu

#### Vinu78

##### New Member
Sorry for typo of "Ho Joe".

#### Joe4

I am sorry, but I have been away for most of the day today, and will be away for a good chunk of tomorrow too.
I might have some time to look at this further tomorrow night. I think it may take some time to work through this.

#### Joe4

Also if the amount comes without decimal, can we force the decimal at 9th pos and display as 123.000000
I am a bit confused on this one, as if we have a number like "83.45", we are just displaying it like "83.45" with zeroes after it. So why would "123" be displayed as "123.000000" and not "123.00"?

So there seems to be a discrepancy in the rule you want.

Also, to make sure we are both on the same page, please post the latest version of the formula you are using.

#### Vinu78

##### New Member
Sorry for the delay.
I think Now I am good. I was expecting some values without decimal (incase), but I was told, I will always receive data with decimal.
Your macro helped me very much. Tremendous job.

Replies
1
Views
309
Replies
7
Views
419
Replies
8
Views
718
Replies
1
Views
237
Replies
3
Views
464

1,127,334
Messages
5,624,089
Members
416,010
Latest member
NJT

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back