Hi all, i hope someone can help. I have a file with just one column, but has 3 component data. The first is "city", the second is the "zipcode" and third is "time". for e.g. Bay Minette 36507 10:00am or Bessemer 35020-35021 8:00am
The issue with the data structure is some cases you can have the city name with two parts to its name separated by a space for e.g. Bay Minette and/or you can have the zip code with a range 35020-35021. I want to preserve the integrity of the name and also keep the range togther and split this data into three columns.
How would i go about doing this?
Hi excelnovice
Try the following and assuming your data is in A1:A?
B1: =LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)
C1: =TRIM(MID(A1,LEN(B1),LEN(A1)-LEN(B1)-LEN(TRIM(RIGHT(A1,7)))))
D1: =SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(A1,7)),"am",""),"pm","")+0
Kevin
Kevin, thanks, i changed the last formula to = TRIM (RIGHT(A1,7)), I tried the "Substitute formule and it was returning a decimal, i wasn't able to follow the formula, maybe you can explain it, if you have the time.Originally Posted by Kevin@Radstock
But, thanks for the speedy response! it worked well and it helped me in understanding excel functions better....
xcelnovice
the decimal is actually returning the correct time. if you format column D to time, it will display correctly.
the formula in B1, searches from left to right, for the first number in a zip code, so if the city name has a number in it this won't work properly.
this is how you can maintain the integrity of the 2 name city like Bay Minette.
the formula in C1, returns the middle portion of cell a1. it calculates the length of the city name returned in B1, and uses this as the starting position in cell a1. it then simply takes off 7 characters from the right of the string in a1. the trim negates the space at the end if the hour has 2 digits.
the formula in d1, converts the text form of the time given to an actual time format, that is why it returns a decimal.
changing to the formula trim(right(a1,7)) works but then you can't do calculations or comparisons as easy on the data, as it is a time stored as text.
I hope this helped with your understanding.
simi
@ Simi
Thanks for that. I forgot to add in my post to format the cells in column D.
@ excelnovice
Using the formula "= TRIM(RIGHT(A1,7))" is OK if you require a text value, but if you use the formula I posted in post #2 it returns a number, formatted as time you can can work with it if required.
Last edited by Kevin@Radstock; 2013-04-25 at 07:50 AM.
Simi/Kevin thanks for help, I don't want to drag this any longer, but i have three questions on the formulas
1) In the formula, C1: =TRIM(MID(A1,LEN(B1),LEN(A1)-LEN(B1)-LEN(TRIM(RIGHT(A1,7))))), as per your explanation the LEN (B1) gives the system the starting postion, then why do we have to do a LEN(A1) - LEN (B1) - LEN(TRIM....), if you know your starting position then all you need to do is TRIM the RIGHT end to figure out what the ZIP Code is, correct?
2) LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1), I understand here you are searching for a digit, but then why do you in the SEARCH function append A1&1234567890 ? aren't you supposed to be searching for a digit within the cell A1 only?
3) On the SUBSTITUTE formula "=SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(A1,7)),"am",""),"PM","")+0", how does adding a "+0" at the end insert "AM" to the result? and why are you doing a SUBSTITUTE within a SUBSTITUTE, can't this formula be written as SUBSTITUTE(TRIM(RIGHT(A1,7)),"am","")+0 ?
Lets see if I can clear it up some.
1) because A1 contains all the data we have to parse it out in sections.
first you take the left say 10 characters which represent the city name. so the search finds a number in the 11th position and then -1 gives us the city name is 10.
next we need the middle portion, the mid() function is used, it requires a position to start, and the length you want to go.
so mid(A1, start position, length) so the start position is 10, the length is 1 more than the length of the zip code, 5 digit zipcode we take 6 characters because that counts the space. the trim will remove the space later. ok so how do we get 6 for our length from the info we have; total length of A1 - start position - 7.
Instead of using the mid() function, you could take the left of the right.
C1, =TRIM(LEFT(RIGHT(A1,LEN(A1)-LEN(B1)),LEN(A1)-LEN(B1)-7))
both of these options require you to figure out where to start or end your selection in the middle of the string.
Then the time you just take the right 7 characters of A1, with the conversions mentioned below.
2) the A1&123456890 this guarantee's there is a number for the search to find. the min portion returns the first position left to right that contained a number.
3) the +0 takes the text version of time 8:00 and converts it to a decimal version of time. again this is useful if you need to do any calculations or comparisons of time. the double substitute replaces the am or pm with "".
You could get the same results by using =TRIM(LEFT(RIGHT(A1,7),5))+0
the point is to get rid of the text portion of the time and convert it from text to a number value so excel can process it.
to be honest I would clean up some of the formulas to.
B1, =TRIM(LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1))
this will give only the name of the city, without any spaces at the end.
C1, =TRIM(MID(A1,LEN(B1)+1,LEN(A1)-LEN(B1)-7))
D1, =TRIM(LEFT(RIGHT(A1,7),5))+0
the help in excel really does offer some good insight on how some of the functions work with examples.
understanding how a function operates will help you know when to use what function.
Simi
Last edited by Simi; 2013-04-25 at 10:21 PM.
Posting Permissions
Reply With Quote
Bookmarks