Formula Help! Extract text from field

[Thang_Nguyen]

Hi everyone, Can someone help me make a formula to extract text.

How can you isolate the text and make it to its own field in excel from the following : (1.5" X 7" 14PT Matte/Dull Finish Postcards w/ Spot UV on back only, No UV on the front)
TO:
Field 1: 1.5" x 7" ; Field 2: 14PT; Field 3: Matte/Dull Finish

[Justin_Barrett]

Welcome to the community, @Thang_Nguyen! :grinning_face_with_big_eyes: That extraction may be possible depending on how consistent certain items are in the line. In short, you need to be able to use the FIND() function (most likely multiple instances) to locate specific pieces in that longer string. Can you share several other examples of the source line? There needs to be some consistency related to the pieces that you’re trying to extract, or it will be very difficult (if not impossible) to accurately isolate what you want.

[Thang_Nguyen]

1.75" X 3.5" 16PT Round Corner BC with Spot UV on front only, No UV Coating on the back
1.75" X 3.5" 16PT Round Corner BC with Spot UV on front only, No UV Coating on the back
1.75" X 3.5" 16PT Round Corner Matte/Dull Finish Business Cards w/ Spot UV on back only, No UV on the front
1.75" X 3.5" 16PT Round Corner Matte/Dull Finish Business Cards w/ Spot UV on back only, No UV on the front
1.75" X 3.5" 16PT Round Corner Matte/Dull Finish Business Cards w/ Spot UV on front only, No UV Coating on the back

Thank you for the reply. The column will have these.

I just want to extract the string to 3 fields which is 1.75" x 3.5"; 16PT; Round Corner Matte/Dull Finish Business Cards w/ Spot UV on front only, No UV Coating on the back

[Justin_Barrett]

Here’s what I came up with:

And here are the formulas. First, {Dimensions}:

LEFT(Name, FIND('"', Name, FIND('"', Name) + 1))

Next, {Type Size}:

MID(Name, LEN(Dimensions) + 2, FIND(" ", Name, LEN(Dimensions) + 2) - LEN(Dimensions) - 2)

Finally, {Description}:

SUBSTITUTE(Name, Dimensions & " " & {Type Size} & " ", "")

[Thang_Nguyen]

Thank you so much Justin I very much appreciate it!

[Thang_Nguyen]

Justin, Thank you for helping for first one. I have a last field that I need help with it. Its way over my head. I hope you can find some time to look in to it. Thank you.

I would like to extract all the shipping information from the HTML Option string. Some Row has more options then others some time 4 or 5 but the structure is all the same.
BEFORE:

[quote=“Thang_Nguyen, post:6, topic:26153, full:true”]
Justin, Thank you for helping for first one. I have a last field that I need help with it. Its way over my head. I hope you can find some time to look in to it. Thank you.

I would like to extract all the shipping information from the HTML Option string. Some Row has more options then others some time 4 or 5 but the structure is all the same.
BEFORE:
<option value="03" selected="selected">Ground Shipping - $5.00</option><option value="12">3 Day Shipping Service - $16.24</option><option value="02">2 Day Air Shipping - $16.64</option><option value="13">Next Day Shipping by 5pm - $17.59</option><option value="01">Next Day Shipping by 12 noon - $17.96</option><option value="14">Next Day Early A.M. - $73.63</option>

AFTER 1 Field:
Ground Shipping - $5.00
3 Day Shipping Service - $16.24
2 Day Air Shipping - $16.64
Next Day Shipping by 5pm - $17.59
Next Day Shipping by 12 noon - $17.96
Next Day Early A.M. - $73.63

[Justin_Barrett · Accepted solution]

My results (two versions, one of which marks the selected option with :white_check_mark:

The formula for {Clean 1}:

TRIM(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(
                            SUBSTITUTE(
                                SUBSTITUTE(
                                    SUBSTITUTE(
                                        Name, " selected=\"selected\"", ""
                                    ), "1\">", ""
                                ), "2\">", ""
                            ), "3\">", ""
                        ), "4\">", ""
                    ),  " value=\"0", ""
                ),  " value=\"1", ""
            ), "<option", ""
        ), "</option>", "\n"
    )
)

And {Clean 2}:

TRIM(
    SUBSTITUTE(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(
                        SUBSTITUTE(
                            SUBSTITUTE(
                                SUBSTITUTE(
                                    SUBSTITUTE(
                                        Name, " selected=\"selected\">", ">✅ "
                                    ), "1\">", ""
                                ), "2\">", ""
                            ), "3\">", ""
                        ), "4\">", ""
                    ),  " value=\"0", ""
                ),  " value=\"1", ""
            ), "<option", ""
        ), "</option>", "\n"
    )
)

[Thang_Nguyen]

Thank you so much Justin!