Code to find a text column, truncate it, change it to numbers, total and weight it

Hey,
Thank you in advance for the help. It means a lot, and will save me a lot of time and headaches.

I am trying to write a code which will take a column of text labelled "Market Cap", cut the text down to 5 characters, convert this to numerals, and then sum it up at the bottom of the column. Then in another column, labelled "Weights" It will take each of the values in the "Market Cap" column and divide it by the total and convert it to a percentage. I need help writing code which will find "Market Cap" and do this.

Ex: Market Cap Market Cap Weights
12.35B Change To 12.35 17.82%
43.54B -------> 43.53 62.81%
13.43B 13.43 19.20%
Sum 69.31

In the "Before" column, the numbers entered are formatted as text. They need to be reformatted as numbers, and the "B" has to be removed

Before

Market Cap

14.23B

98.87B

12.34B

After
Market Cap	Weightages
14.23	11.34%
98.87	78.82%
12.34	9.84%
Sum: 125.44

Thanks oeldere,
I see what you mean, and how by attaching a file it can clear a lot of the confusion. Take a look at the attached file

I was hoping for a VB code that could do this since the position of the "Market Cap" Column, as well as the number of items in the column are variable.