Help with excel formatting (change data info)

Associate
Joined
19 Oct 2002
Posts
804
Location
Leicester
Im trying to edit an speed camera POI file so i can use with a program on my N95 mobile.

It needs to be in the following format
Code:
GATSO@40
GATSO
MOBILE@60
TRUVELO@0
RedLight@30
SPECS@70

But what i have is (as an example)

Code:
GATSO:1@64
GATSO:10@80
GATSO:1000@48
GATSO:1001@48
GATSO:1003@48
GATSO:1004@48

I need to remove the numbers between the GATSO and the @

I have uploaded the file im messing around with herehttp://www.starcote.co.uk/kk.csv
 
Not sure you can do it with just Excel - but a little VBA and searching through the string removing what is needed and dropping the required output in a new cell would work.

Edit: Using the ':' and the '@' as your reference points should be a doddle.
 
Last edited:
Probably easier to do this in Access.

I've just imported the CSV into a new database - here's the query to get out what you need, the 'Final' field will give you the format you were after...

Code:
SELECT Kk.Field1, Kk.Field2, Kk.Field3, 
Left([field3],InStr([field3],":")-1) AS Type, 
IIf(InStr([field3],"@")>0,Right([field3],Len([field3])-InStrRev([field3],"@")+1),"") AS Rating, 
[Type]+[Rating] AS Final 
FROM Kk;
 
Last edited:
Easy if you know how.

Here you go: http://rotunda.comoj.com/kk_done.csv

If you want to do it yourself:

  1. Highlight the column with the data you want to split (C in this case).
  2. Go to Data > Text to Columns > Delimited > Other > : > Finish
  3. This will give you a new column (D) with the data in the format "xxx@xx" and only "GATSO" in the (C) column now.
  4. Select the new column and then go to Data > Text to Columns > Delimited > Other > @ > Finish
  5. This gives you a second new colum (E) with just the digits after the "@" and the digits before the "@" now in (D).
  6. Finally in column F1 type in the following formula: =Concatenate(C1,"@",E1) and copy it down the column.
 
Easy if you know how.

Here you go: http://rotunda.comoj.com/kk_done.csv

If you want to do it yourself:

  1. Highlight the column with the data you want to split (C in this case).
  2. Go to Data > Text to Columns > Delimited > Other > : > Finish
  3. This will give you a new column (D) with the data in the format "xxx@xx" and only "GATSO" in the (C) column now.
  4. Select the new column and then go to Data > Text to Columns > Delimited > Other > @ > Finish
  5. This gives you a second new colum (E) with just the digits after the "@" and the digits before the "@" now in (D).
  6. Finally in column F1 type in the following formula: =Concatenate(C1,"@",E1) and copy it down the column.

Have to say, learnt something there. Question though - Is using the concatenate function in that way the same as doing:

=C1 & "@" & E1

I assume it is. Depending on the circumstances concatenate should make things easier... I like it :)
 
Don't forget the power of the Excel FIND, LEFT and MID functions either. I'm not in front of Excel now, but something along these lines would also do the trick, when filled down (where data is in column A):
=left(A1,find(":",A1)-1)&mid(A1,find("@",A1),99)

You could replace the 99 with something more sophisticated like len(A1)-find("@",A1)+1, but that's overkill.
 
Back
Top Bottom