How to count and recognize number and strings withing a cell in vba?

Question

I have a list in column B that contains order ID's from different marketplaces.
I want in column F the name of the marketplace.

The way to recognize them is according to the length of the string (some are numbers only, some others are number and hyphens, 12345 = wholesale, 123-6789123-1210112 = amazon), others by some specific character (e.g. 1234567E = gogo, or 1234OB = open box).
I want to do it in VBA.

This is my code so far.

Sub profit()
    Dim x As Long
    Dim lastrow As Long
    lastrow = Sheets("profit april 28 to may 11").Cells(Rows.Count,"b").End(xlUp).Row
       For x = 6 To lastrow
          If Cells(x, 1) = Cells(x, 2) Then
           Cells(x, 6).Value = "wholesale"
            Else: x = x + 1

        End If
     Next x
End Sub

This code works fine but only gives me wholesale orders by comparing values in column A and B. For the rest of ID's I don't have a reference column (like A). So I need to count the characters on the strings. But some strings have the same length, then I need to look for a second condition (12345678 and 1234567E have the same length but differ in one character). I would like to know how to count characters within a cell in vba and how to find specific character withing a string in VBA?

Stack Overflow is not a code for me site. What have you tried? With the information you have provided this question would be considered too broad. So the only help I can give is to use a table with the ID and corresponding text. Then use the Range.Find() or Application.Match() functions to find the row and get the corresponding cell in that row. — Scott Craner, May 12 '16 at 18:01
Scott, I find a way to find "wholesale" orders, but I got stuck trying to add the other variables. This is my code so far.....Sub profit() Dim x As Long Dim lastrow As Long lastrow = Sheets("profit april 28 to may 11").Cells(Rows.Count, "b").End(xlUp).Row For x = 6 To lastrow If Cells(x, 1) = Cells(x, 2) Then Cells(x, 6).Value = "wholesale" Else: x = x + 1 End If Next x End Sub......with this particular order ID im use column A as reference. But i don't have the same reference for the rest of ID's — DHI, May 12 '16 at 18:03
Please put the code in the Original Post using edit. Then tell us specifically what it is doing in error, on which line and what error you are receiving. — Scott Craner, May 12 '16 at 18:06
you can count string characters with [`Len()`](https://msdn.microsoft.com/en-us/library/dxsw58z6(v=vs.90).aspx) function and find a specific character withing a string with [`Instr()`](https://msdn.microsoft.com/it-it/library/8460tsh1(v=vs.90).aspx) function. if you show some data we could be more helpful — user3598756, May 12 '16 at 18:44

score 1 · Accepted Answer · edited May 23 '17 at 10:28

It's too long to explain fully here and your question is pretty broad, but what you want is regular expressions. If you do some searching online and in stackoverflow, you should be able to figure out how to build some patterns to match your data and use that. Here are a couple resources to get you started though.

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

https://msdn.microsoft.com/en-us/library/ms974570.aspx

How to count and recognize number and strings withing a cell in vba?

1 Answers1