VBA Right characters in string after "-"

[supermansteel]

Trying to get this France-FR;France-FR;France-FR to FR|FR|FR

'Looping through all the Rows in my worksheet
Lastreq = Range("C" & Rows.count).End(xlUp).Row
Set req_Range = Sheets("Range").Range("B1:AD" & Lastreq
LastRow = Range("D" & Rows.Count).End(xlUp).Row
for i = 2 to LastRow

'Lookup Value from another Sheet
req = Sheets("Lookup").Range("E" & i).Value
seg = Application.WorksheetFunction.Vlookup(req, req_Range,26,False)


seg1 = WorkSheetFunction.Substitute(seg,";","|") 'Converts Semi-colon to |
seg2 = Split(seg1,"|")
seg3 = Join(seg2,"|")

'Then I was pasting the Results of this here
Range("AA"& i).Value = seg3

Next

This was working, but now I learned that I only need everything to the right of the "-". I was trying Right, Mid and Find and instr Functions, but keep getting error and can't figure exactly out how to write this to correctly make this as my output FR|FR|FR because of this being an array once you split the items.

Please help

 

[oldbrewer]

search for France-FR and replace with FR

 

[Rick Rothstein]

Assuming your example text is a terrible example and that your real text can have the part vary instead of all being the same... it is a little hard following your code... can you give us a clear description of where your text is, how the string you say you want to modify comes into existence and where you want the results to go>

 

[supermansteel]

Assuming your example text is a terrible example and that your real text can have the part vary instead of all being the same... it is a little hard following your code... can you give us a clear description of where your text is, how the string you say you want to modify comes into existence and where you want the results to go>

I am taking 2 Sheets and combining them into 1 sheet, so that is why I have the Vlookup and I am looping through every Row to the LastRow taking the ID Column from the one Sheet to the same ID column in the other sheet.
The Problem is that this is a Form that has Multiple Values for each Country (so they could be AUSTRIA-AT;ITALY-IT, etc. and I am doing a Split function so that when the user clicks on that cell the form pops up and displays a country onto each Line like this:
Austria-AT
Italy-IT
France-FR

However, the form isn't displaying the Values correctly and I recently learned because I need to have the Values in this format FR|FR|IT|AT in order for the Form to read each item when it launches. There is apparently a sheet that has these broken into lookup values:

ID	Country
FR	France
IT	Italy

<tbody>
</tbody>

So i would need to either get all the characters before the "-" and do a vlookup in order to display the ID Column separated by the "|" and have it loop through all the values in the same cell because there could be upwards of 10 Countries in one cell, but I need to end Result in the cell to display only the ID column separated by the "|"...

The Original data is in this format that I am trying to split in FR|FR|IT|AT shown below:
France-FR;France-FR;Italy-IT;Austria-AT

 

[Rick Rothstein]

I am taking 2 Sheets and combining them into 1 sheet, so that is why I have the Vlookup and I am looping through every Row to the LastRow taking the ID Column from the one Sheet to the same ID column in the other sheet.
The Problem is that this is a Form that has Multiple Values for each Country (so they could be AUSTRIA-AT;ITALY-IT, etc. and I am doing a Split function so that when the user clicks on that cell the form pops up and displays a country onto each Line like this:
Austria-AT
Italy-IT
France-FR

However, the form isn't displaying the Values correctly and I recently learned because I need to have the Values in this format FR|FR|IT|AT in order for the Form to read each item when it launches. There is apparently a sheet that has these broken into lookup values:

ID	Country
FR	France
IT	Italy

<tbody>
</tbody>

So i would need to either get all the characters before the "-" and do a vlookup in order to display the ID Column separated by the "|" and have it loop through all the values in the same cell because there could be upwards of 10 Countries in one cell, but I need to end Result in the cell to display only the ID column separated by the "|"...

Your description has a lot of generalities without much specifics (remember, I cannot see your workbook, so I don't know where anything is or where on whatever sheet it is located. Let me see if I can short-circuit what I need to know in order to write any code for you. This text...

AUSTRIA-AT;ITALY-IT, etc.

does it run down a specific column? If so, what is the sheet name, what is the column number or letter designation and what is the row number containing the first text string? I also need to know... where is the output (the pipe delimited data) supposed to go?

 

[supermansteel]

Your description has a lot of generalities without much specifics (remember, I cannot see your workbook, so I don't know where anything is or where on whatever sheet it is located. Let me see if I can short-circuit what I need to know in order to write any code for you. This text...

AUSTRIA-AT;ITALY-IT, etc.

does it run down a specific column? If so, what is the sheet name, what is the column number or letter designation and what is the row number containing the first text string? I also need to know... where is the output (the pipe delimited data) supposed to go?

The Country information is found on Sheet("Requirements_Report") IN Column Y.
The Main Sheet is ("Requirement and Rules") where I am doing a Vlookup from Column 5 in this sheet to Requirements_Report column 2 because they match ID = ID and it gives me back the Value in Column Y. Value in Column Y = AUSTRIA-AT;ITALY-IT, etc.

I only need to get value from Column Y in this format in order for my form to read it: AT|IT|FR, etc which is put into Sheets("Requirements and Rules").Range("AO" & i).Value = New Column Y Format.

Thanks

 

[Rick Rothstein]

Give this macro a try...

Sub AbbreviationsAfterDash()
  Dim Addr As String, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Sheets("Requirements_Report")
  Set WS2 = Sheets("Requirements and Rules")
  Addr = "'" & WS1.Name & "'!" & WS1.Range("Y2", _
         WS1.Cells(Rows.Count, "Y").End(xlUp)).Address
  With WS2.Range("AO2").Resize(Range(Addr).Rows.Count)
    .Cells = Evaluate("IF(LEN(" & Addr & "),"";""&" & Addr & ","""")")
    .Replace ";*-", "|", xlPart
    Addr = "'" & .Parent.Name & "'!" & .Address
    .Cells = Evaluate(Replace("IF(LEN(@),MID(@,2,LEN(@)),"""")", "@", Addr))
  End With
End Sub

 

[supermansteel]

Give this macro a try...

Sub AbbreviationsAfterDash()
  Dim Addr As String, WS1 As Worksheet, WS2 As Worksheet
  Set WS1 = Sheets("Requirements_Report")
  Set WS2 = Sheets("Requirements and Rules")
  Addr = "'" & WS1.Name & "'!" & WS1.Range("Y2", _
         WS1.Cells(Rows.Count, "Y").End(xlUp)).Address
  With WS2.Range("AO2").Resize(Range(Addr).Rows.Count)
    .Cells = Evaluate("IF(LEN(" & Addr & "),"";""&" & Addr & ","""")")
    .Replace ";*-", "|", xlPart
    Addr = "'" & .Parent.Name & "'!" & .Address
    .Cells = Evaluate(Replace("IF(LEN(@),MID(@,2,LEN(@)),"""")", "@", Addr))
  End With
End Sub

Thanks Rick for you assistance. I am not sure that is what is what I am looking for.

I was working on this some more and this is what I got:
dsmt = WorksheetFunction.Substitute(g,";",",") 'This makes the data France-FR,France-FR,France-FR to make it into an array.

Then I was still doing my split function
dsmt2 = Split(dsmt,",")

Then I was going to loop through and put the Value into the Requirements and Rules sheet Column AO by doing this:
For Each item In dsmt2
Range(g & i).Value = Right(item,Len(item)-WorksheetFunction.Find("-",item) & "|"
Next item

This is getting me close to my result however, it is only putting 1 Value in the cell FR| and I am trying to get FR|FR|FR...when I try to do this Right function in a For Loop I keep getting an error and I am not sure what I am doing wrong with that..

How do I loop through each of the values and put all values into the same cell?

 

[supermansteel]

Finally figured it out...

For x = LBound(dsmt2) to UBound(dsmt2)
dsmt3 = dsmt3 & "|" & Right(dsmt2(x),Len(dsmt2(x))-WorksheetFunction.Find("-",dsmt2(x)))
Next x
Range(g & i).Value = Right(dsmt3,Len(dsmt3)-1)

AND it perfectly displays FR|FR|FR now!!

 

[Rick Rothstein]

Thanks Rick for you assistance. I am not sure that is what is what I am looking for.

How can you not be sure... either my code worked or it didn't... did you try my code? Or did you not try it because you did not see any loops in it? Just so you know, my code deliberately does not use any loops, but it will process all of the rows of data you have... and, just so you know, my macro did work for me using the data I think you have in your cells when I tested it before I posted it.