Help

Re: Formula Help! Extract text from field

Solved
Jump to Solution
1154 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Thang_Nguyen
5 - Automation Enthusiast
5 - Automation Enthusiast

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

1 Solution

Accepted Solutions

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

15%20AM

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

See Solution in Thread

7 Replies 7

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.

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

Here’s what I came up with:

54%20PM

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
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much Justin I very much appreciate it!

Thang_Nguyen
5 - Automation Enthusiast
5 - Automation Enthusiast

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

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

15%20AM

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
5 - Automation Enthusiast
5 - Automation Enthusiast

Thank you so much Justin!