Separating data in spreadsheet

Leesha
5StarLounger
Posts: 1140
Joined: 05 Feb 2010, 22:25

Separating data in spreadsheet

Post by 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

User avatar
HansV
Administrator
Posts: 72885
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Separating data in spreadsheet

Post by HansV »

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

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

This can be filled down.
Regards,
Hans

User avatar
TonyE
3StarLounger
Posts: 361
Joined: 24 Jan 2010, 14:24
Location: Buckinghamshire, England

Re: Separating data in spreadsheet

Post by 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 /
Tony

Leesha
5StarLounger
Posts: 1140
Joined: 05 Feb 2010, 22:25

Re: Separating data in spreadsheet

Post by Leesha »

Thank you both!!

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Separating data in spreadsheet

Post by 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
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 04 Dec 2021, 16:45, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

Leesha
5StarLounger
Posts: 1140
Joined: 05 Feb 2010, 22:25

Re: Separating data in spreadsheet

Post by Leesha »

Thank you!

hamster
NewLounger
Posts: 8
Joined: 10 Mar 2021, 22:57

Re: Separating data in spreadsheet

Post by 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