replace the 4th letter from the right

[ExcelPeon]

I have a large set of data that contains SKUs with different number of characters. Each SKU can range from 10-20 characters. The only thing that is constant is the last 4 characters. Which formula can i use to only replace the 4th character from the right?

Example
SKU1-U-101
SKUUU-U-101

replace "U" with "A"

Results
SKU1-A-101
SKUUU-A-101

Thanks in advance!

 

[sandy666]

with PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SKU1-U-101[/td][td][/td][td=bgcolor:#E2EFDA]SKU1-A-101[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SKUUU-U-101[/td][td][/td][td]SKUUU-A-101[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.AddColumn(Source, "result", each Text.Replace([raw],"-U-","-A-")),
    ROC = Table.SelectColumns(Replace,{"result"})
in
    ROC[/SIZE]

or

=SUBSTITUTE(A2,"-U-","-A-")

but post more representative examples

 

[ExcelPeon]

Thank you so much!

 

[sandy666]

You are welcome
Have a nice day

 

[ExcelPeon]

Sorry but what if its

[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]aw[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]result[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]S-U-SKU1-U-101[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]S-U-SKU1-A-101[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]S-U-SKUUU-U-101[/TD]
[TD][/TD]
[TD]S-U-SKUUU-A-101[/TD]
[/TR]
</tbody>[/TABLE]

 

[ExcelPeon]

RAW RESULTS
[TABLE="class: cms_table_cms_table_head"]
<tbody>[TR]
[TD="bgcolor: #DDEBF7"]S-U-SKU1-U-101[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]S-U-SKU1-A-101[/TD]
[/TR]
[TR]
[TD]S-U-SKUUU-U-101[/TD]
[TD][/TD]
[TD]S-U-SKUUU-A-201[/TD]
[/TR]
</tbody>[/TABLE]

sorry for the back and forth, i can't edit my post on here.

 

[sandy666]

that is why I said "post more representative examples"

===
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]raw[/td][td][/td][td=bgcolor:#70AD47]result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]SKU1-U-101[/td][td][/td][td=bgcolor:#E2EFDA]SKU1-A-101[/td][/tr]

[tr=bgcolor:#FFFFFF][td]SKUUU-U-101[/td][td][/td][td]SKUUU-A-101[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]S-U-SKU1-U-101[/td][td][/td][td=bgcolor:#E2EFDA]S-U-SKU1-A-101[/td][/tr]

[tr=bgcolor:#FFFFFF][td]S-U-SKUUU-U-101[/td][td][/td][td]S-U-SKUUU-A-101[/td][/tr]
[/table]

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Dup = Table.DuplicateColumn(Source, "raw", "raw - Copy"),
    ExtractLast = Table.TransformColumns(Dup, {{"raw - Copy", each Text.End(_, 6), type text}}),
    ExtactBefore = Table.TransformColumns(ExtractLast, {{"raw", each Text.BeforeDelimiter(_, "-", {1, RelativePosition.FromEnd}), type text}}),
    Replace = Table.ReplaceValue(ExtactBefore,"U","A",Replacer.ReplaceText,{"raw - Copy"}),
    Merge = Table.CombineColumns(Replace,{"raw", "raw - Copy"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"result")
in
    Merge[/SIZE]

 

[sandy666]

or

[SIZE=1]// Table1 (2)
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Replace = Table.AddColumn(Source, "result", each Text.BeforeDelimiter([raw], "-", {1, RelativePosition.FromEnd})&"-"&Text.Replace(Text.AfterDelimiter([raw], "-", {1, RelativePosition.FromEnd}),"U","A")),
    ROC = Table.SelectColumns(Replace,{"result"})
in
    ROC[/SIZE]

 

[ExcelPeon]

Wow. Thank you so much! What if i wanted to increase the letter by one to get to the next letter?

[TABLE="class: cms_table_head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #5B9BD5"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]aw[/COLOR][/TD]
[TD][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]result[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]SKU1-A-101[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]SKU1-B-101[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]SKUUU-B-101[/TD]
[TD][/TD]
[TD]SKUUU-C-101[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #DDEBF7"]S-U-SKU1-U-101[/TD]
[TD][/TD]
[TD="bgcolor: #E2EFDA"]S-U-SKU1-X-101[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]S-U-SKUUU-U-101[/TD]
[TD][/TD]
[TD]S-U-SKUUU-X101[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

you could use a formula

=SUBSTITUTE(A1,"-U-","-A-")