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
Separating data in spreadsheet
-
- Administrator
- Posts: 78475
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Separating data in spreadsheet
Let's say you have such a text string in A2.
=--MID(A2,21,FIND("/",A2,21)-21)
This can be filled down.
=--MID(A2,21,FIND("/",A2,21)-21)
This can be filled down.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 361
- Joined: 24 Jan 2010, 14:24
- Location: Buckinghamshire, England
Re: Separating data in spreadsheet
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 /
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
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Separating data in spreadsheet
Thank you both!!
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Separating data in spreadsheet
Example or Hans formula in a VBA Evaluate range thing
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
Alan
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
=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)")
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
You can find me at DocAElstein also
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Separating data in spreadsheet
Thank you!
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: Separating data in spreadsheet
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