Formula Or Code For Below Results In Columns B & C.

[Dazzawm]

I have column A as laid out below. I would like the results that are in column B and C please. If it is a formula a UDF would be advantageous. Thanks.

New Numbers Template.xlsx
	A	B	C
1
2	31498N	31498,31498N,31498R	31498-OE,31498-OS
3	31506N	31506,31506N,31506R	31506-OE,31506-OS
4	34021N	34021,34021N,34021R	34021-OE,34021-OS
5	16324N	16324,16324N,16324R	16324-OE,16324-OS
6	30629N	30629,30629N,30629R	30629-OE,30629-OS
7	16037N	16037,16037N,16037R	16037-OE,16037-OS
8	31499N	31499,31499N,31499R	31499-OE,31499-OS
Sheet1

 

[Joe4]

Pretty easy with some basic functions.

Formula for cell B2 (and copy down):

=LET(x,LEFT(A2,5),x&","&x&"N,"&x&"R")

Formula for cell C2 (and copy down):

=LET(x,LEFT(A2,5),x&"-OE,"&x&"-OS")

 

[Dazzawm]

Pretty easy with some basic functions.

Formula for cell B2 (and copy down):

=LET(x,LEFT(A2,5),x&","&x&"N,"&x&"R")

Formula for cell C2 (and copy down):

=LET(x,LEFT(A2,5),x&"-OE,"&x&"-OS")

Both formulas leave of the last two numbers like below.

New Numbers Template.xlsx
	A	B	C
1
2	WA31498N	WA314,WA314N,WA314R	WA314-OE,WA314-OS
3	WA31506N	WA315,WA315N,WA315R	WA315-OE,WA315-OS
4	WA34021N	WA340,WA340N,WA340R	WA340-OE,WA340-OS
5	WA16324N	WA163,WA163N,WA163R	WA163-OE,WA163-OS
6	WA30629N	WA306,WA306N,WA306R	WA306-OE,WA306-OS
7	WA16037N	WA160,WA160N,WA160R	WA160-OE,WA160-OS
8	WA31499N	WA314,WA314N,WA314R	WA314-OE,WA314-OS
Sheet1

Cell Formulas
Range	Formula
B2:B8	B2	=LET(x,LEFT(A2,5),x&","&x&"N,"&x&"R")
C2:C8	C2	=LET(x,LEFT(A2,5),x&"-OE,"&x&"-OS")

 

[Joe4]

That is the danger when the example you post are not representative of all the data you may be working with!
In your original example, all your entries were exactly 6 characters long. In this last one, it is 7 characters long.
It is really important that you give us ALL the important details, if you want an answer that will work for all your cases.
(The key to getting a good answer is to ask a good, complete question).

If your entries could have various lengths, and it is just the last character that should be dropped, then change the formulas to this:

For cell B2:

=LET(x,LEFT(A2,LEN(A2)-1),x&","&x&"N,"&x&"R")

For cell C2:

=LET(x,LEFT(A2,LEN(A2)-1),x&"-OE,"&x&"-OS")

 

[Dazzawm]

That is the danger when the example you post are not representative of all the data you may be working with!
In your original example, all your entries were exactly 6 characters long. In this last one, it is 7 characters long.
It is really important that you give us ALL the important details, if you want an answer that will work for all your cases.
(The key to getting a good answer is to ask a good, complete question).

If your entries could have various lengths, and it is just the last character that should be dropped, then change the formulas to this:

For cell B2:

=LET(x,LEFT(A2,LEN(A2)-1),x&","&x&"N,"&x&"R")

For cell C2:

=LET(x,LEFT(A2,LEN(A2)-1),x&"-OE,"&x&"-OS")

View attachment 112545

Sorry that was my fault, thanks for your time. A UDF is not possible I suppose in this instance where there are 2 formulas?

 

[Joe4]

Sorry that was my fault, thanks for your time. A UDF is not possible I suppose in this instance where there are 2 formulas?

A UDF would be possible, but it is usually recommended to never use a UDF when a simply formula will do.
You want to do something distinctly different in column B versus column C, so you would need to different UDFs anyway (unless you wanted to make one longer complex one).
So I really see no advantage to using a UDF here.

If you are looking for a VBA solution, I think a Procedure would probably better than a UDF here, where you loop through all the rows with data in column A and put the desired results in columns B and C. However, the caveat there is if you change the data in column A afterwards, you would need to re-run the VBA code (or use an Worksheet_Change Event Procedure which would update it in real-time).

 

[Dazzawm]

That was an error on my part, the WA in front would never be there I forgot to remove it.

 

[Fuji]

If you like UDF,
Use in cell like
B2;
=FILTER(WA(A2:A100),A2:A100<>"")

Function WA(r As Range)
    Dim a, i As Long, s
    a = r.Value
    ReDim Preserve a(1 To UBound(a, 1), 1 To 2)
    For i = 1 To UBound(a, 1)
        s = a(i, 1)
        If s <> "" Then
            s = Left$(s, 5)
            a(i, 1) = Join(Array(s, s & "N", s & "R"), ",")
            a(i, 2) = Join(Array(s & "-OE", s & "-OS"), ",")
        End If
    Next
    WA = a
End Function

 

[Dazzawm]

A UDF would be possible, but it is usually recommended to never use a UDF when a simply formula will do.
You want to do something distinctly different in column B versus column C, so you would need to different UDFs anyway (unless you wanted to make one longer complex one).
So I really see no advantage to using a UDF here.

If you are looking for a VBA solution, I think a Procedure would probably better than a UDF here, where you loop through all the rows with data in column A and put the desired results in columns B and C. However, the caveat there is if you change the data in column A afterwards, you would need to re-run the VBA code (or use an Worksheet_Change Event Procedure which would update it in real-time).

I would be obliged with a VBA, I will use on many different files then and keep in my personal macro workbook.

 

[Joe4]

I would be obliged with a VBA, I will use on many different files then and keep in my personal macro workbook.

See Fuji's post above.