changing a number and letter combination to a calculated number (e.g. 500g to 0.1 Kg)

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3163226%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3Echanging%20a%20number%20and%20letter%20combination%20to%20a%20calculated%20number%20(e.g.%20500g%20to%200.1%20Kg)%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3163226%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3Ewhat's%20the%20best%20way%20to%20change%20the%20packa%20weight%20in%20the%20far%20right%20column%20to%20just%20be%20a%20numerical%20value%20(i'm%20looking%20to%20convert%20to%20kilos)%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348011i93F447C345BEAC7D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22cmckernan93_0-1644850077033.png%22%20alt%3D%22%5C%26quot%3Bcmckernan93_0-1644850077033.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3163226%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3ETraining%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

what's the best way to change the packa weight in the far right column to just be a numerical value (i'm looking to convert to kilos) 

cmckernan93_0-1644850077033.png

 

1 Reply

@cmckernan93 

Let's say the values are in D2 and down.

In another column, enter the following formula in row2, then fill down:

=IF(RIGHT(D2,2)=" G",--LEFT(D2,LEN(D2)-2)/1000,IF(RIGHT(D2,3)=" KG",--LEFT(D2,LEN(D2)-3),D2))