Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
Concatenate cells in Pocket Excel | Excel Discussion (Misc queries) | |||
Concatenate Function will not work | Excel Discussion (Misc queries) | |||
Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Concatenate two halves of a phone number | Excel Worksheet Functions |