To Reformat cells in certain conditions

Vinu78

New Member
Joined
Aug 5, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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

Excel macro example.JPG


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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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

Can you please help me.
 
Upvote 0
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.

Can you please help me.

Regards
Vinu
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,585
Messages
6,114,519
Members
448,575
Latest member
hycrow

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top