Separate numerical and text values of differing lengths from a single cell into separate columns

How do I separate numerical and text values of differing lengths from a single cell into separate columns? For example, I'm trying to total the "AREA" Column, but due to having "SF" in the cell, it will not return a numerical value. The numerical values to the left are of differing lengths, making this problematic. The provided image here is what I have done manually, but I would like for this to be in the form of a formula or function.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

Mike TV

@Todd Ray

Here's a method. Example sheet:

First you have to find the # of characters in the Area column with this formula in the # Chars column:

=LEN(Area@row)

Then you can remove the " SF" from the field with this formula in the Area Value column:

=LEFT(Area@row, ([# Chars]@row - 3))

However, SmartSheet is going to treat that Area Value column as text. So if you want to do further calculations on the Area Value column results you'll need to do something like this which is in the Further Calculations column:

=VALUE([Area Value]@row) / 10

Dan W

@Mike TV

@Todd Ray

I see you got it worked out

Yes I did not account for the space between the numbers and SF so adding a space in " SF" works just fine. Cheers!

=VALUE(SUBSTITUTE(AREA@row, " SF", ""))

All comments

Mike TV

@Todd Ray

Here's a method. Example sheet:

First you have to find the # of characters in the Area column with this formula in the # Chars column:

=LEN(Area@row)

Then you can remove the " SF" from the field with this formula in the Area Value column:

=LEFT(Area@row, ([# Chars]@row - 3))

=VALUE([Area Value]@row) / 10

Dan W

Is it always SF? If so you could remove the SF with.

=VALUE(SUBSTITUTE(AREA@row, "SF", ""))

Then add the SF into UNIT with ="SF"

Todd Ray

@Mike TV - Thank you very much for the assistance here. It all worked out as directed in your response except for the following formula =VALUE([Area Value]@row) / 10. I just had to change the "10" to "1", which worked perfectly for all the values.

@Dan W - It is always SF, but I get the following response #INVALID VALUE when trying to utilize the proposed formula. Any thoughts on why this may be occurring? Thank you

Mike TV

@Todd Ray

=VALUE(SUBSTITUTE(AREA@row, " SF", ""))

Dan W has a more elegant solution than mine. His method was broken because he forgot to take out the space with the substitute function. The above should work for you possibly better than my method of doing it.

Dan W

@Mike TV

@Todd Ray

I see you got it worked out

Yes I did not account for the space between the numbers and SF so adding a space in " SF" works just fine. Cheers!

=VALUE(SUBSTITUTE(AREA@row, " SF", ""))

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.