merge many characters(letters , numbers, symbols)from three columns to one

[abdelfattah]

hello
what I want is very complicated what I want as picture when i write in column b,c,d automatically merge in column a so the first should write BS after this letters sometimes I OR J OR T theses depend on column d the first letter after this should bring what contain in column c after this bring just the numbers or numbers with symbols without any letter

CODE	tire	type	origin
BSTB251757013	175/70R13	B25	THI
BSJ613V19514	195R14C	613V	JAP
BSJVSDL26525**	26.5R25**	VSDL	JAP
BSIVMTP180033**	1800R33**	VMTP	INDO

 

[Rick Rothstein]

Can you make use of a UDF (user defined function)? If so...

Function CreateCode() As String
  Dim X As Long, RowNum As Long, Tire As String
  RowNum = Application.Caller.Row
  Tire = Cells(RowNum, "B").Value
  For X = 1 To Len(Tire)
    If Mid(Tire, X, 1) Like "[0-9*]" Then CreateCode = CreateCode & Mid(Tire, X, 1)
  Next
  CreateCode = "BS" & Replace(Left(Cells(RowNum, "D").Value, 1), "*", "J") & Cells(RowNum, "C").Value & CreateCode
End Function

NOTE: You were not clear about those asterisks in Column D so I assumed those are what put the letter "J" after the "BS" characters in the output.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CreateCode just like it was a built-in Excel function. For example,

=CreateCode()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[abdelfattah]

wow ! very impressive ! about asterisks I no know what's the problem the cells are written jap but when copy by tool of Mr. excel XL2BB it shows asterisks any way just I would one thing is there way do that automatically without write the UDF? I mean when I finish written in column d then shows in column a

 

[abdelfattah]

the star *** is short country japan

 

[Rick Rothstein]

I mean when I finish written in column d then shows in column a

Are you saying you do not want to display anything until all three columns are filled in? If so, use this formula in cell A2 before copying it down (as far as you think you will ever need)...

=IF(COUNTA(B2:D2)=3,CreateCode(),"")

 

[abdelfattah]

I mean if do that by worksheet change event I don't have to pull down every time I need it
but I no know how write the code worksheet change event

 

[abdelfattah]

I try writing this but it doesn't work

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(4)) Is Nothing Then
   LR = Range("d" & Rows.Count).End(xlUp).Row
   Range("a2:a" & LR).Formula = "=IF(COUNTA(B2:D2)=3,CreateCode(),"")"

End If
End Sub

 

[Rick Rothstein]

Give this Change event code a try...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim X As Long, Tire As String, CreateCode As String
  If Not Intersect(Target, Columns("B:D")) Is Nothing Then
    If Target.CountLarge > 1 Then
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
    ElseIf Application.CountA(Intersect(Target.EntireRow, Columns("B:D"))) = 3 Then
      Tire = Cells(Target.Row, "B").Value
      For X = 1 To Len(Tire)
        If Mid(Tire, X, 1) Like "[0-9*]" Then CreateCode = CreateCode & Mid(Tire, X, 1)
      Next
      Cells(Target.Row, "A") = "BS" & Replace(Left(Cells(Target.Row, "D").Value, 1), "*", "J") & Cells(Target.Row, "C").Value & CreateCode
    End If
  End If
End Sub

Note 1: You no longer need the CreateCode UDF as I bundled its code directly into the procedure above.

Note 2: The above procedure assumes you will only enter data into Columns B:D one cell at a time. If you attempt to copy/paste more than one cell's worth of values into those columns, the attempt will be ignored and the original cells will be returned to their prior value.

Note 3: This procedure does not put formulas in the cells in Column A. Since the procedure monitors all changes in Columns B:D, it will automatically update Column A's value just like a formula would have done.

 

[abdelfattah]

well don ! this what I want just I would know this part of code I always see but I don't understand what means what's job

If Target.CountLarge > 1

 

[Rick Rothstein]

The CountLarge property is the same as the Large property except that it can handle a larger number of cells like if you tried to clear all of the cells on a worksheet (if you used large, the code would crash). The code line itself is checking whether you are trying to change more than one cell (see Note 2 in my last message).