# Formula Help! Extract text from field

Topic Labels: Formulas
Solved
2435 7
cancel
Showing results for
Did you mean:
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:

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"
)
)
``````
7 Replies 7
18 - Pluto

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.

5 - Automation Enthusiast

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

18 - Pluto

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} & " ", "")``
5 - Automation Enthusiast

Thank you so much Justin I very much appreciate it!

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:

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"
)
)
``````
5 - Automation Enthusiast

Thank you so much Justin!