extract part of a cell's text

ty.sjomeling100911

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!

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 "-"?