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([email protected]; 2; 2)

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

=MID([email protected], 2, 2)

Try something like this for the second one.

=RIGHT([email protected]; 4)

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

=RIGHT([email protected], 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([email protected], LEN([email protected]) - FIND("*", [email protected]))

.

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([email protected], LEN([email protected]) - FIND("*", [email protected])))

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!