Replacing characters in a text string with certain criteria

bingobango

New member
Joined
Apr 11, 2022
Messages
2
Reaction score
0
Points
0
Excel Version(s)
Microsoft 365
Hello,

I am attempting to automate the replacement of certain characters in a text string with a single formula. The format of the cell may be either number or text, if it matters.

Specifically, if the first two characters in the string are any of "09", "10", "15", "16" then I want these to be replaced with "NE".

If the first two characters are, "11", "12", "13", "14" then I want these to become NW.

01, 02, 07, 08 to become SE and 03, 04, 05, 06 to become SW.

I often use Defined Names for given column for this, but in this instance I am getting a #SPILL! error when trying to do so.

Any wizardry that can be imparted is most appreciated!

Sample workbook attached with desired outcome manually entered.
 

Attachments

  • ReplaceSpecifiedTextInString - Copy.xlsx
    13.5 KB · Views: 5
SPILL is happening becaus the formula refers to a whole column of cells and the formula is in a table. To keep things as they are (within a table) the formula:
Code:
=VLOOKUP(LEFT([@[String of characters]], 2),$E$8:$F$23,2,0) & MID([@[String of characters]],3,20)
in cell B2 should copy itself down the table.

If the result is outside the table then you can use:
Code:
=VLOOKUP(LEFT(String_of_characters, 2),E8:F23,2,0) & MID(String_of_characters,3,20)
in any single cell and it should give a column of results as long as there is nothing in the 14 cells below that cell.

In both those formulae, you can replace
Code:
E8:F23
with
Code:
{"09","NE";"10","NE";"15","NE";"16","NE";"11","NW";"12","NW";"13","NW";"14","NW";"01","SE";"02","SE";"07","SE";"08","SE";"03","SW";"04","SW";"05","SW";"06","SW"}
then you won't need that range anywhere.

Is there any logic behind these numbers referring to what appear to be the quadrants of a compass? If so there may be a shorter formula.
 
SPILL is happening becaus the formula refers to a whole column of cells and the formula is in a table. To keep things as they are (within a table) the formula:
Code:
=VLOOKUP(LEFT([@[String of characters]], 2),$E$8:$F$23,2,0) & MID([@[String of characters]],3,20)
in cell B2 should copy itself down the table.

If the result is outside the table then you can use:
Code:
=VLOOKUP(LEFT(String_of_characters, 2),E8:F23,2,0) & MID(String_of_characters,3,20)
in any single cell and it should give a column of results as long as there is nothing in the 14 cells below that cell.

In both those formulae, you can replace
Code:
E8:F23
with
Code:
{"09","NE";"10","NE";"15","NE";"16","NE";"11","NW";"12","NW";"13","NW";"14","NW";"01","SE";"02","SE";"07","SE";"08","SE";"03","SW";"04","SW";"05","SW";"06","SW"}
then you won't need that range anywhere.

Is there any logic behind these numbers referring to what appear to be the quadrants of a compass? If so there may be a shorter formula.

This worked great, thank you.

The numbers do refer to quadrants of a compass, using the township/range system here in Alberta, Canada.

TWP.JPG

Individually, 3, 4, 5, 6 refer to legal subdivisions, and together represent the SW quarter section of the larger full section (I parsed out some of the text in the string so as to make it non-identifying).

Legal subdivisions (1-16) are less commonly used than quarter section (NW, NE, SW, SE), but the software that spits out the Excel file does it by subdivision.

I won't normally have the table array in there, so using this worked perfect once I removed the table and just named the specified range:

Code:
VLOOKUP(LEFT(String_of_characters, 2),{"09","NE";"10","NE";"15","NE";"16","NE";"11","NW";"12","NW";"13","NW";"14","NW";"01","SE";"02","SE";"07","SE";"08","SE";"03","SW";"04","SW";"05","SW";"06","SW"},2,0) & MID(String_of_characters,3,20)
 
Is there any logic behind these numbers referring to what appear to be the quadrants of a compass? If so there may be a shorter formula.
With that arrangement:
2022-04-13_102836.png
I don't see a straightforward way of determining the quadrant. (If there were lots of similarly arranged squares of different sizes with the same snaking of incrementing values from bottom right to top left it might be worth it - if only as an academic exercise.)
 
Try this

=VLOOKUP(LEFT([@[String of characters]],2),$E$8:$F$23,2,0)&MID([@[String of characters]],FIND("-",[@[String of characters]]),(LEN([@[String of characters]])-2))
 
Back
Top