extract part of a cell's text

Hello,

New smartsheet user here.

I have cells that contain dimensions of objects. A cylinder, for example, looks like this: Ø120*12. I need a way to extract just the text to the right of the *, in this case 12, to another cell. Another example: Ø18*1000 would return 1000.

How do you do this?

Thanks!

Find more posts tagged with

Sheets

Formulas

Solution Center

Comments

Andrée Starå

Hi Ty,

Yes, there is.

In my example, I'm using the functions MID and RIGHT.

Try something like this for the first one.

=MID(Dimension@row; 2; 2)

The same version but with the below changes for your and others convenience.

=MID(Dimension@row, 2, 2)

Try something like this for the second one.

=RIGHT(Dimension@row; 4)

The same version but with the below changes for your and others convenience.

=RIGHT(Dimension@row, 4)

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Paul Newcome

I suggest using the RIGHT function and automating how many characters to pull so that you don't have to worry about hardcoding whether it is 4, 2, or 23.

=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))

The LEN function gives you the total count of characters. The FIND function looking for the asterisk gives you how many characters from the left the asterisk is found.

Ø120*12

is 7 characters long. Subtract the position of the asterisk (5), and you tell the RIGHT function to pull 2 digits.

Ø18*1000

is 8 characters long. Subtract the position of the asterisk (4), and you tell the RIGHT function to pull 4 digits.

This will maximize your automation by basing it off of the data itself instead of manual calculations.

If you were planning on using this data that you are pulling as actual numbers in the future as opposed to just displaying them as a text string, you would need to wrap the entire formula in a VALUE function.

=VALUE(RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row)))

Heather Duff

@Paul Newcome As always, thank you for this - I was looking for a formula to pull only the text to the right of a character. I'm so thankful that you already had it written out for me!

PeggyLang

@Paul Newcome I was looking for EXACTLY this solution! THANK YOU!!!!

=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))

Chizu Hieida

@Paul Newcome thank you! this is what I needed!!! :)

=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))

Marilen.Navarro103391

@Paul Newcome

I need a way to extract just the text MEGA MALL. I have 3,000 rows like this one.

LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410

Paul Newcome

@Marilen.Navarro103391 What are the consistencies in your text strings? Will it always be after the 5th "-"? Will it always follow "Lead-"? Will it always come immediately before the last "-"?

Sign in to join the conversation!

Quick Links

Trending discussions

TODAY() Function Not Auto-Updating Unless Sheet is Opened and Saved
I’m facing an issue where the TODAY() function in Smartsheet doesn’t refresh automatically each day. It only updates when the sheet is manually opened and saved. The sheet uses TODAY() for date-based calculations, and these values are shown in a dashboard. The dashboard data stays outdated until I open the source sheet —…
Work around for Year transitions when using the WEEKNUMBER formula
Currently, I have a project starting in September 2025 and ending in June 2026. How can I use the WEEKNUMBER formula to create September 29,2025 as week 1 and continue the numbering sequence through June 1, 2026. I also need the formula to flow well when date changes are made to the plan.
How to disable popups that obscure my formula?
I'm editing formulas in a Sheet Summary. However, I'm prevented from seeing the formula by an annoying "helpful" popup that obscures my view. I can't figure out how to get rid of the popup. Does anyone know?