8 replies to this topic

[Charles Syrett]

I've been asked to edit some street map indexes that list streets by name, followed by tab, followed by page and index grid reference. For example: Bloor St. W. (tab) 39--M14. The main thing I'm being asked to check is that the grid reference is corresponds to the correct page number. (In the example, I would ensure that M14 really is on page 39, and not on, say, 49.)

I'm tempted to bring the whole shebang into Excel and separate the pages from the grid refs as separate columns, but I'm not sure that there's any automatic way to do this. Sitting down and banging a tab manually in between each of these is not my idea of fun. Any Excel/Word experts out there that can suggest something?

Thanks!

Charles Syrett
Map Graphics
http://www.mapgraphics.com

[jrat]

if your index is a set amount of characters you could use the LEFT and RIGHT functions. My excell is a bit rusty so check the help. But they can be used to get only the left/right most x characters.

[Paul H]

I've been asked to edit some street map indexes that list streets by name, followed by tab, followed by page and index grid reference. For example: Bloor St. W. (tab) 39--M14. The main thing I'm being asked to check is that the grid reference is corresponds to the correct page number. (In the example, I would ensure that M14 really is on page 39, and not on, say, 49.)

I'm tempted to bring the whole shebang into Excel and separate the pages from the grid refs as separate columns, but I'm not sure that there's any automatic way to do this. Sitting down and banging a tab manually in between each of these is not my idea of fun. Any Excel/Word experts out there that can suggest something?

Thanks!

Charles Syrett
Map Graphics
http://www.mapgraphics.com

When opening the file in Excel the text import wizard will prompt you to choose Delimited as the original data type, then choose Tab as the delimeter.

[natcase]

Excel will import as tab delimited. You could use a formula using SEARCH and the LEFT and RIGHT functions. In this case,

LEFT ([source], SEARCH([source],"-",1)-1)

would return "39" and

RIGHT ([source], (LEN ([source]) - SEARCH([source],"-",2))

should return "M14".

Alternatively, you could replace all "--" with tab in a text editor and import as tab delimited.

For complex reconfiguring, I usually use FileMaker, but any decent database program would do it. I find it has more tools for selecting and altering. FME would do this too.

[Charles Syrett]

Thanks, Nat. I don't actually know where to start on Excel formulas, so the simple Find>Replace option looks good. However, none of the apps I've tried -- TextEdit, Word, Excel -- will let me replace with tab. I just get bounced back up to the Find field. Is there some alternative way to enter tab into the Replace field so that it will actually replace with the tab?

Charles Syrett
Map Graphics
http://www.mapgraphics.com

Excel will import as tab delimited. You could use a formula using SEARCH and the LEFT and RIGHT functions. In this case,

LEFT ([source], SEARCH([source],"-",1)-1)

would return "39" and

RIGHT ([source], (LEN ([source]) - SEARCH([source],"-",2))

should return "M14".

Alternatively, you could replace all "--" with tab in a text editor and import as tab delimited.

For complex reconfiguring, I usually use FileMaker, but any decent database program would do it. I find it has more tools for selecting and altering. FME would do this too.

[Hans van der Maarel]

Yeah, this is the kind of stuff FME really can shine at. I've often tried to do similar things in Excel or OpenOffice and it's usually a lot of trial and error.

One thing you could try is importing as tab delimited, then replace the "--" in the 2nd column with some (single) character that won't occur anywhere in the index, like "@". Then re-save it as CSV but specify that same characted as seperator, and don't let it quote any output values. Re-import and specify the special character again as seperator. That *could* (would? should?) do the trick.

[gregory]

Charles,

I know from you activity at georeference.org that you're also a manifold user. Why don't you give a try ?
As far as I unterstood you problem you can do it using Copy firt token/ Copy last token in tabular view of your data.
http://www.manifold....ext_strings.htm

Hope it helps,
Gregory

[Unit Seven]

To do a find and replace with a tab in a text editor you need to type ^t for Ultraedit or InDesign to insert a tab character maybe this will work in Notepad as well....or in this case I would just open in excel and use Data > Text to columns to split the column at -- When I found this tool I found myself using it a lot.

[Charles Syrett]

....or in this case I would just open in excel and use Data > Text to columns to split the column at -- When I found this tool I found myself using it a lot.

Bingo -- that did it! Right under my nose the whole time.

Charles Syrett
Map Graphics
http://www.mapgraphics.com