place hyphens in a string

[EDUCATED MONKEY]

Object to place hyphens between inistials of name Only not just replace white spaces
please note that i do not want a hyphen between the last initial and the surname
Example G K Aalborg would become G-K Aalborg

this is an amendment to my earlier post, the reply I received maid me think that I would be better starting over, it seems that I am lacking the understand of so of the syntax so for that I apologise and hope help is still forth coming <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
This is what I have so far
Sub SplitName2()<o></o>
'<o></o>
' SplitName2 Macro<o></o>
' Macro recorded <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:date Month="7" Day="25" Year="2011">25/07/2011</st1:date> by Peter Hayward<o></o>
'<o></o>
' Keyboard Shortcut: Ctrl+Shift+S<o></o>
Dim Q As Integer<o></o>
Dim k As Integer<o></o>
Dim S As String<o></o>
Dim MyChar As Variant<o></o>
Dim WordLen As Integer<o></o>
Dim Position As Integer<o></o>
Dim fwsp As Boolean<o></o>
<o></o>
Sheets("REMOVE UNWANTED ITEMS").Select<o></o>
Q = Worksheets("REMOVE UNWANTED ITEMS").Range("D1").Value 'how many rows for D1<o></o>
For k = 1 To Q<o></o>
S = Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 6).Value<o></o>
<o></o>
fwsp = True<o></o>
<o></o>
For Position = WordLen To 1 Step -1<o></o>
<o></o>
MyChar = Mid(S, Position, 1)<o></o>
<o></o>
Select Case MyChar.Value<o></o>
<o></o>
Case Is <> ""<o></o>
Next Position<o></o>
Case Is = ""<o></o>
fwsp = False<o></o>
Next Position<o></o>
Case Is = ""<o></o>
If fwsp = False Then<o></o>
S = Replace(S, " ", "-")<o></o>
Next Position<o></o>
End If<o></o>
<o></o>
End Select<o></o>
Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 7) = S<o></o>
Next k<o></o>
S<o></o>
End Sub
<o></o>
I am now getting a next with out for error, which occurs in either if the Next Position ifcomment out the first one, it is probably due to the fact there are in side the case statement /loop can you tell how I rearrange the code to get over this problem also if you think I am tackling this problem wrongly I would rather be pointed in the right direction even if it mean starting over as I need a clear understanding of what and why I am doing things so I am able to maintain these sub later if needed as I can not keep relying on you good guy to get me out of the whole
<o></o>

 

[hiker95]

EDUCATED MONKEY,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[EDUCATED MONKEY]

not sure what you like a screen shot of here is out of the attached code if i flip the bool to false hope this gives you an idea what is wrong i am using win xp professional and office xp 2003 this is the output
<TABLE style="WIDTH: 98pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130 x:str><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: lime; WIDTH: 98pt; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl31 height=17 width=130>G-K-Aalborg</TD></TR></TBODY></TABLE>

Option Explicit
Sub SplitName2()
'
' SplitName2 Macro
' Macro recorded 25/07/2011 by Peter Hayward
'
' Keyboard Shortcut: Ctrl+Shift+S
Dim Q As Integer
Dim k As Integer
Dim S As String
Dim MyChar As Variant
Dim WordLen As Integer
Dim Position As Integer
Dim fwsp As Boolean
    Sheets("REMOVE UNWANTED ITEMS").Select
    Q = Worksheets("REMOVE UNWANTED ITEMS").Range("D1").Value 'how many rows for D1
    For k = 1 To Q
    S = Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 6).Value
    
    fwsp = True
 
    For Position = WordLen To 1 Step -1
    MyChar = Mid(S, Position, 1)
    
     Select Case MyChar.Value
          Case Is <> ""
      Next Position
          Case Is = ""
          fwsp = False
             Next Position
         Case Is = ""
         If fwsp = False Then
           S = Replace(S, " ", "-")
              Next Position
              End If
              
    End Select
Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 7) = S
Next k
 S
End Sub
Sub SplitName3()
'
' SplitName3 Macro
' Macro recorded 25/07/2011 by Peter Hayward
'
' Keyboard Shortcut: Ctrl+Shift+G
Dim Q As Integer
Dim k As Integer
Dim S As String
Dim MyChar As Variant
Dim WordLen As Integer
Dim Position As Integer
Dim fwsp As Boolean
 
    Sheets("REMOVE UNWANTED ITEMS").Select
    Q = Worksheets("REMOVE UNWANTED ITEMS").Range("D1").Value 'how many rows for D1
   ' For k = 1 To Q
   k = 1
    
     S = Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 6).Value 'test ok
    WordLen = Len(S) 'tested ok
     fwsp = True
    For Position = WordLen To 1 Step -1      'tested ok
    MyChar = Mid(S, Position, 1)
fwsp = True
If MyChar = " " And fwsp = True Then
fwsp = False
ElseIf MyChar = " " And fwsp = False Then
S = Replace(S, " ", "-")
ElseIf MyChar <> " " And fwsp = False Then
End If
 
                        Worksheets("REMOVE UNWANTED ITEMS").Cells(10, 10).Value = S
  Next Position
     
'Next k
Worksheets("REMOVE UNWANTED ITEMS").Cells(k, 7).Value = S
End Sub

 

[hiker95]

EDUCATED MONKEY,

We need to see screenshots of your data in worksheet REMOVE UNWANTED ITEMS, before (in its raw state) and after (manually formatted by you) as if the macro has been run.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:

 

[Rick Rothstein]

You really did not give us a complete picture of what you need, so I made a couple of assumptions. One, your initials appear only at the beginning of the String value. Two, the initials are always upper case. With those two assumptions in mind, here is a macro for you to consider using to put a hyphen between the first two initials...

Sub PutHyphenBetweenInitials()
  Dim Cell As Range
  For Each Cell In Worksheets("REMOVE UNWANTED ITEMS").Range("F1:F" & Cells(Rows.Count, "F").End(xlUp).Row)
    If Cell.Value Like "[A-Z] [A-Z] *" Then Cell.Value = Replace(Cell.Value, " ", "-", , 1)
  Next
End Sub

 

[EDUCATED MONKEY]

Hi the charters in the string are case variable the first column of data is approx 900 rows deep if you need data to test then I could post part of the list if that helps. The over all problem is that different people have entered the data and there has been a lack of consistence now I am left with what amounts to the correct data but with a human element thrown in <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>

 

[Rick Rothstein]

if you need data to test then I could post part of the list if that helps. The over all problem is that different people have entered the data and there has been a lack of consistence

Yes, example data that shows us the variety of names you have to deal with would definitely be helpful; otherwise we can only guess at what your data looks like and, as you should be able to see, our guesses are rarely correct. For future questions you might ask in forums, it will always a good idea to include sample data (and the layout of that data even) that shows us what you actually have to deal with.

 

[EDUCATED MONKEY]

hello i have submitted it called it raw data
please let me know if all is well
regards pete

 

[Rick Rothstein]

hello i have submitted it called it raw data
please let me know if all is well
regards pete

You submitted it where??? As far as I know, this forum does not provide for attachments. However, you don't really need to provide an entire file for your particular request... just copy/paste, say 10 to 15 names that you need to process and make the ones you select show a range of possible names you have to deal with. I'm particularly interested in seeing if the initials you want the hyphen in can appear anywhere in the name or only at the beginning; whether you have names with only one intial (so nothing should be done) or names with no initials at all; whether the names are always in the same order or can the names be reversed with, say, a comma separating them. Show as much of the variety of names you have to deal with as you can... the more information we have, the better able we will be able to devise a solution.