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!

Comments

  • Andrée Starå
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    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])))

    thinkspi.com

  • Heather Duff
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!