Separating data in spreadsheet

[Leesha]

Hi,
I have a spreadsheet with a column that needs to be split apart. I've tried with past code I've gotten but I just can't get it.

The cell data looks like this:

XXXXXXX Daily Sales 2098/2210 Morse Road

I need to extract the first set of numbers into a new column. In this example it would be 2098. The format should always be the same as it is listed here with the exception that the X's are actually the name of the entity. There will always be 7 characters in the entity name.

Thanks!
Leesha

[HansV]

Let's say you have such a text string in A2.

=--MID(A2,21,FIND("/",A2,21)-21)

This can be filled down.

[TonyE]

Another way of doing this without using formulas is by using Flash Fill. This method is useful if it's a one off task.

If the first 2 values in cells A1 & A2 are:
XXXXXXX Daily Sales 2098/2210 Morse Road
XXXXXXX Daily Sales 8764/2210 Morse Road

In B1 type 2098, in B2 start typing 8764, after the first couple of characters Excel will likely guess what you want and remaining values will show in light grey, just press enter. It does not matter what length the number is before the /

[Leesha]

Thank you both!!

[Doc.AElstein]

Example or Hans formula in a VBA Evaluate range thing

Code: Select all

Sub EvalRange()  '  https://eileenslounge.com/viewtopic.php?p=290448#p290448
Dim Rng As Range
 Set Rng = Range("A2:A10")  '  <--- CHANGE TO SUIT YOUR DATA RANGE 
    '                                   =       --MID(          A2       ,21,FIND( "/" ,        A2         ,21)-21)
 Let Rng.Offset(0, 2).Value = Evaluate("=if({1},--MID(" & Rng.Address & ",21,FIND(""/""," & Rng.Address & ",21)-21))")
   '          (0, 2)    pastes output 2 columns to the right, so  in column C                  (0, 4)  would paste  in column E    etc.  
End Sub

Note: You probably don’t need the extra
=if({1}, xxxxxxxxxxxxxxxxxxxxxxxxxxxxx )
bit for Excel 2016 + , but it does not do any harm to leave it in for compatibility with Excel 2013 and lower
So for Excel 2016 + , this would do it just as well

Code: Select all

                           = Evaluate("=--MID(" & Rng.Address & ",21,FIND(""/""," & Rng.Address & ",21)-21)")

Alan

[Leesha]

Thank you!

[hamster]

another way with PowerQuery

Code: Select all

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ETR = Table.TransformColumns(Source, {{"src", each Text.Middle(_, 20, 4), type text}})
in
    ETR