VBA code to select first digit/letter of each column

[Sekiro]

Hello.

I need some of your help that may allow me to save some time.

I need a VBA code that on the second column (B) ,"Unique codes" , to automatically appear the first letter/digit of each of the other columns, but the full country code and if one of the columns it's empty, this will not effect the other letters.

For example for Spain, the unique code: VA7IT , for Romania: 7NI8GB and for Poland: AA2T3ESM

Thank you for all your help.

 

[HighAndWilder]

Hello.

I need some of your help that may allow me to save some time.

I need a VBA code that on the second column (B) ,"Unique codes" , to automatically appear the first letter/digit of each of the other columns, but the full country code and if one of the columns it's empty, this will not effect the other letters.

For example for Spain, the unique code: VA7IT , for Romania: 7NI8GB and for Poland: AA2T3ESM

View attachment 119583

Thank you for all your help.

Dates and Times.xlsm
	A	B	C	D	E	F	G	H	I
1	Company	Unique Codes	Address Line 1	Address Line 2	Address Line 3	City	Postal Code	Country Code	Province
2	Spain	VA7IT	Via America		Amsterdam		73623	IT
3	Romania	7NI8GB	73 Street	Near office		Istanbul	83727	GB
4	Poland	AA2T3ESM	Avenida 63	Alcala	28	Toledo	3425	ES	Madrid
Sheet3

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(C2,1) & LEFT(D2,1) & LEFT(E2,1) & LEFT(F2,1) & LEFT(G2,1) & H2 & LEFT(I2,1)

 

[Micron]

Best if you identified your columns; better still if you used XL2BB to post data. You don't need vba but if that's what you want then this un-elegant code may suffice:

Public Function Sekiro() As String
Dim rng As Range, cel As Range
Dim sht As Worksheet
Dim str As String, i As Long

i = Application.Caller.Parent.Cells.row
Set sht = ActiveSheet
Set rng = sht.Range("C" & i & ":" & "H" & i)
For Each cel In rng
    If cel <> "" Then str = str & Left(cel, 1)
Next
str = str & sht.Range("I" & i)
If sht.Range("J" & i) <> "" Then str = str & sht.Range("J" & i)
Sekiro = str
   
MsgBox Sekiro
End Function

The call would be as =Sekiro() in whatever cells you want to construct the value in. The function has to be in a standard module.

 

[Peter_SSs]

If you decided that you don't really need a macro then you could try this

Sekiro.xlsm
	A	B	C	D	E	F	G	H	I
1	Company	Unique Codes	Address Line 1	Address Line 2	Address Line 3	City	Postal Code	Country Code	Province
2	Spain	VA7IT	Via America		Amsterdam		73623	IT
3	Romania	7NI8GB	73 Street	Near office		Istanbul	83727	GB
4	Poland	AA2T3ESM	Avenida 63	Alcala	28	Toledo	3425	ES	Madrid
Sheet1

Cell Formulas
Range	Formula
B2:B4	B2	=CONCAT(IF(C$1:I$1="Country Code",C2:I2,LEFT(C2:I2,1)))

If you do really want vba then it could be done like this.

Sub Codes()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Formula2 = "=CONCAT(IF(C$1:I$1=""Country Code"",C2:I2,LEFT(C2:I2,1)))"
    .Value = .Value
  End With
End Sub

 

[Sekiro]

Hello,

Thank you all for the help, but when I try to write the formula or even the VBA code, I get this,

 

[Peter_SSs]

Looks like your cells are formatted as Text. Format them as General and then re-enter the first formula and copy it down or run the vba.