Log In
Register
Home
New Member
October 10, 2020
Offline
In column A I have text like mentioned below. I am trying to separate the City Name and date in two separate columns. I don't need the other text.
Alameda 1-10-20 CSV-PAY SUMMARY RUN.csv
Palm Harbor 1-15-20 Hourly CSV-PAY SUMMARY RUN.csv
Palm Harbor 1-15-20 Salary CSV-PAY SUMMARY RUN.csv
Basically, there are various combinations like above and there are different city names. I tried using mid, search, left, right, but its not helping.
I need name of city in one column and date in another.
Will appreciate help.
See Answer
Trusted Members
December 20, 2019
Offline
There will definitely be a better way with power query, where you can split on a number, but in the meantime, this seems to work on the small data set provided.
=TRIM(MID(A2,1,MATCH(1,--ISNUMBER(MID(A2,ROW($A$1:INDIRECT("$A$"&$B$1)),1)+0),0)-1))
In cell B1 we have the max length of the text in column A, so i can create an array of numbers 1 to the max length. This goes inside the second MID to check if each character is a number, match then finds the first number.
So, we then have the first mid which is basically the text in A2 starting at the first character and extracting up to the character before the first number.
Trim just removes the spaces
If there are any cities that contain a number, it won’t work!
You need to test it on a much larger dataset
Answers Post
October 5, 2010
Offline
Hi m r,
Attached is a solution using Power Query. I also did one with formulae, different to Purfleet. You can't have too many options.
To extract the city
=TRIM(MID(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE($A1,"Hourly ",""), "Salary ",""), " CSV-PAY SUMMARY RUN.csv",""), 1,FIND(C1,A1)-2))
and the date
=TRIM(RIGHT(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE ($A1,"Hourly ",""), "Salary ",""), " CSV-PAY SUMMARY RUN.csv", ""),8))
These only work with data as provided in your example. If your complete data is different then adjustments will need to be made.
Regards
Phil
The following users say thank you to Philip Treacy for this useful post:
PurfleetNew Member
October 10, 2020
Offline
Thanks guys! Really appreciate. I’ll try to add these formulas in my dataset. However, I too came up with another solution
For City: I used search function to find hypen and subtracted 2 from that and nested that in Left function to extract city names. And similar with slightly different search function to get dates and extracted using Right function. Only catch is if there’s another word in between city names and date then that too gets extracted, but i can add one more column and separate those. Thats and much appreciated!
1 Guest(s)
