Excel Custom format

[JSH720]

I have data that looks like this 13N25W25. I want it to look like this 13N 25W-25. How do I build a custom format or something to change it automatically? I also have data that looks like 13N 25W 25.in the same column. it all should be formatted the same. Thanks.

 

[Rick Rothstein]

I have data that looks like this 13N25W25. I want it to look like this 13N 25W-25. How do I build a custom format or something to change it automatically? I also have data that looks like 13N 25W 25.in the same column. it all should be formatted the same. Thanks.

You cannot format that cell as it contains text (you can only format numbers in Excel). Here is a formula solution (which means your values are in a different column)...

=SUBSTITUTE(SUBSTITUTE(A1,"N","N "),"W","-")

We can add more SUBSTITUTE calls if you need to handle S and E. Also, if you want to change the values directly within the cells themselves, you can use a VBA macro (if you want to run it manually) or event code (if you want the change to happen automatically), but in order to write it, we need to know where your data is on the worksheet.

 

[JSH720]

I saw that I never thanked you, my apologies. Thank you so much! I would be interested in the event code and VBA code. The date is all in one column. I have multiple spreadsheets that I would need to do it on, but the data is always on one column on an individual spreadsheet. So I would add whichever code, event ofr VBA, that I use for each spreadsheet.

Thanks.

 

[Rick Rothstein]

I saw that I never thanked you, my apologies. Thank you so much! I would be interested in the event code and VBA code. The date is all in one column. I have multiple spreadsheets that I would need to do it on, but the data is always on one column on an individual spreadsheet. So I would add whichever code, event ofr VBA, that I use for each spreadsheet.

Are the multiple spreadsheets all in one workbook? If so, I can write an event for the workbook that would handle the individual sheets... that way you would only have one event procedure handling all of your sheets instead of having to have multiple individual event procedures (one per worksheet). The only problem with doing it this way is i need to know the names of each worksheet and which column on each worksheet that needs to be covered by the procedure. Alternately, if the column on each worksheet has a header cells which is identically the same on each sheet, I can have the code look for that text on the header row (is that row 1 on your worksheets?) and let the code determine which column needs to be monitored. Since I don't know the layout of your sheets, you need to guide me on this.

 

[JSH720]

Here they are:
[TABLE="width: 242"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WORKCOPY (Kyle Edit )[/TD]
[/TR]
[TR]
[TD]NOTES[/TD]
[/TR]
[TR]
[TD]Kyle Edit ORIG[/TD]
[/TR]
[TR]
[TD]SCHEDULE[/TD]
[/TR]
[TR]
[TD]MAP[/TD]
[/TR]
[TR]
[TD]TX ROBERTS MORSE[/TD]
[/TR]
[TR]
[TD]HE-4891[/TD]
[/TR]
[TR]
[TD]HE-L102569[/TD]
[/TR]
[TR]
[TD]NT SOLD1[/TD]
[/TR]
[TR]
[TD]DI WELLSRANCH ET AL[/TD]
[/TR]
[TR]
[TD]BERGERT AND MORE[/TD]
[/TR]
[TR]
[TD]BE-12N21W[/TD]
[/TR]
[TR]
[TD]HE_LSE_605-239[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[/TR]
[TR]
[TD]RM_ASSGN[/TD]
[/TR]
[TR]
[TD]UNIQUE DESC[/TD]
[/TR]
[TR]
[TD]NT SOLD2[/TD]
[/TR]
[TR]
[TD]DE_ ASSGN[/TD]
[/TR]
[TR]
[TD]CU-ASSGN[/TD]
[/TR]
[TR]
[TD]WA_ASSIGN[/TD]
[/TR]
[TR]
[TD]RO_ASSIGN[/TD]
[/TR]
[TR]
[TD]HE_ASSIGN

I will need to do this over time multiple times to update spreadsheets. Also, I'll need to be able to do on other spreadsheets as I work on other projects.

This will be a godsend, so I thank you in advance.


[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Here they are:
[TABLE="width: 242"]
<colgroup><col></colgroup><tbody>[TR]
[TD]WORKCOPY (Kyle Edit )[/TD]
[/TR]
[TR]
[TD]NOTES[/TD]
[/TR]
[TR]
[TD]Kyle Edit ORIG[/TD]
[/TR]
[TR]
[TD]SCHEDULE[/TD]
[/TR]
[TR]
[TD]MAP[/TD]
[/TR]
[TR]
[TD]TX ROBERTS MORSE[/TD]
[/TR]
[TR]
[TD]HE-4891[/TD]
[/TR]
[TR]
[TD]HE-L102569[/TD]
[/TR]
[TR]
[TD]NT SOLD1[/TD]
[/TR]
[TR]
[TD]DI WELLSRANCH ET AL[/TD]
[/TR]
[TR]
[TD]BERGERT AND MORE[/TD]
[/TR]
[TR]
[TD]BE-12N21W[/TD]
[/TR]
[TR]
[TD]HE_LSE_605-239[/TD]
[/TR]
[TR]
[TD]MD[/TD]
[/TR]
[TR]
[TD]RM_ASSGN[/TD]
[/TR]
[TR]
[TD]UNIQUE DESC[/TD]
[/TR]
[TR]
[TD]NT SOLD2[/TD]
[/TR]
[TR]
[TD]DE_ ASSGN[/TD]
[/TR]
[TR]
[TD]CU-ASSGN[/TD]
[/TR]
[TR]
[TD]WA_ASSIGN[/TD]
[/TR]
[TR]
[TD]RO_ASSIGN[/TD]
[/TR]
[TR]
[TD]HE_ASSIGN
[/TD]
[/TR]
</tbody>[/TABLE]

What are these? If they are sheet names, I need to know the column on each of them that the event procedure needs to monitor.