How do I extract a series of numbers along with a single letter followed by another series of numbers?

Question

The problem that I'm facing is that I have an entire column that has text separated by _ that contains pixel size that I want to be able to extract but currently can't. For example:

           A                                  
Example_Number_320x50_fifty_five
Example_Number_One_300x250_hundred
Example_Number_two_fifty_728x49

I have tried using Substitute function to grab the numbers which works but only grabs the numbers when I need something like: 320x50 instead I'm getting 0, as I'm not sure how to exactly extract something like this. If it was consistent I could easily do LEFT or RIGHT formula's to grab it but as you can see the data varies.

The result that I'm looking for is something along the lines of:

           A                        |          B   
Example_Number_320x50_fifty_five    |       320x50
Example_Number_One_300x250_hundred  |       300x200
Example_Number_two_fifty_728x49     |       728x49

Any help would be much appreciated! If any further clarification is needed please let me know and I'll try to explain as best as I can!

-Maykid

I guess you can use regex https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — colinD, Apr 24 '19 at 17:09

K.Dᴀᴠɪs · Accepted Answer · 2019-04-24T17:15:22.900

I would probably use a Regular Expressions UDF to accomplish this.

First, open up the VBE by pressing Alt + F11.
Right-Click on VBAProject > Insert > Module

Then you can paste the following code in your module:

Option Explicit

Public Function getPixelDim(RawTextValue As String) As String

    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+x\d+"
        If .Test(RawTextValue) Then
            getPixelDim = .Execute(RawTextValue)(0)
        End If
    End With

End Function

Back to your worksheet, you would use the following formula:

=getPixelDim(A1)

Looking at the pattern \d+x\d+, an escaped d (\d) refers to any digit, a + means one or more of \d, and the x is just a literal letter x. This is the pattern you want to capture as your function's return value.

Works like a charm! Thank you so much! – Maykid Apr 24 '19 at 17:19 — Maykid, Apr 24 '19 at 17:19

score 2 · Answer 2 · answered Apr 24 '19 at 17:17

Gosh, K Davis was just so fast! Here's an alternate method with similar concept.

Create a module and create a user defined function like so.

Public Function GetPixels(mycell As Range) As String

    Dim Splitter As Variant
    Dim ReturnValue As String
    Splitter = Split(mycell.Text, "_")

    For i = 0 To UBound(Splitter)
        If IsNumeric(Mid(Splitter(i), 1, 1)) Then
            ReturnValue = Splitter(i)
            Exit For
        End If
    Next

    GetPixels = ReturnValue

End Function

In your excel sheet, type in B1 the formula =GetPixels(A1) and you will get 320x50.

How do you create a user defined function?

Developer tab

Use this URL to add Developer tab if you don't have it: https://www.addintools.com/documents/excel/how-to-add-developer-tab.html

Click on the highlighted areas to get to Visual Basic for Applications (VBA) window.

Create module

Click Insert > Module and then type in the code.

Use the user defined function

Note how the user defined function is called.

Not only the code itself is functional, but the visuals are certainly great for those who are unfamiliar with the VBE. Well done putting this together — K.Dᴀᴠɪs, Apr 24 '19 at 17:35

How do I extract a series of numbers along with a single letter followed by another series of numbers?

2 Answers2

How do you create a user defined function?

Linked