Un - Concatenate?
Hello there,
I have a large list of police records and one of the fields contains their, ID Code, Area and Rank seperated by spaces. I know there is a LEN and FIND fuction but how do I conbine these?? Thankyou in advance Judith |
Hi
Why don't you insert the necessary columns (2 in this case), then do Data|Text to Columns, select delimited, select Space (and unselect Tab) to split it into columns "JudithJubilee" wrote: Hello there, I have a large list of police records and one of the fields contains their, ID Code, Area and Rank seperated by spaces. I know there is a LEN and FIND fuction but how do I conbine these?? Thankyou in advance Judith |
find will enable you to get the location of the blank spaces in the text string, which can then be used with =left, =right, =mid and =len. The resulting formulae can be a bit cumbersome but if the ID and area code are always the same number of characters they can be simplified. These should work for variable lengths: Cell A1 is: 1531 dorset constable =LEFT(A1,FIND(" ",A1)) returns 1531 =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)) returns Dorset =RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)) returns Constable Hope this helps -- Alex Delamain ------------------------------------------------------------------------ Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273 View this thread: http://www.excelforum.com/showthread...hreadid=386055 |
All times are GMT +1. The time now is 06:42 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com