Extracting Numbers from a Cell Containing Both Text & Numbers

Allison_S

Hi All -

I am uploading a .csv file into a Smartsheet sheet. One of the columns within the .csv file is formatted to contain '$ prior to the numbers within the cell (so when opening in Excel, it will not convert the cell contents into scientific notation). I need to remove the '$ text from the cell, so I can sum the value of the column in a separate reporting sheet. I was hoping to add a neighboring column to the Smartsheet sheet (that I am uploading the .csv file to) that would automatically extract only the number value of the neighboring cell, so I can link that column to my reporting sheet and obtain the numeric sum of each cell. Hopefully this makes sense, any formula suggestions? Appreciate any help I can get! 

 

Thank you! 

Allison

Paul Newcome

Try something along the lines of 

 

=VALUE(SUBSTITUTE([Original Column]@row, "$", ""))

 

This will remove the $ from the string and change it from a text string representing a number to an actual number that can be used in other formulas.