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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Some videos you may like

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

Vinu78

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,564
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 5, 2020
Messages
12
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top