Breaking a name into characters

[rcbricker]

I need to take a name such as 444rich and break it into its individual characters. Each seperate character will be in its own cell like "text to columns" does with multiple data in the same field.

CAn this be done and if so what is the easiest way?

Thanks

[mdmackillop]

Try
Sub Splits()
Dim i as Long
For i = 1 To Len(ActiveCell)
ActiveCell.Offset(, i) = Mid(ActiveCell, i, 1)
Next
End Sub

[rcbricker]

you are too fast on the draw. We just realized that the fix won't work because we found that some of the data in the names changes. I need to change the fourth character in a string to "t" regardless of what that character is. So I no longer need to break the name up, just change the fourth character.

[mdmackillop]

=REPLACE(C9,4,1,"T")

[rcbricker]

Try
Sub Splits()
Dim i as Long
For i = 1 To Len(ActiveCell)
ActiveCell.Offset(, i) = Mid(ActiveCell, i, 1)
Next
End Sub



This didn't work for what I am doing now, but it might later on. I put it into a sheet and tried it and it didn't work. What cell does the text need to be in to get it to run?

[rcbricker]

You are awesome.

It gets a little more complicated and I apologize for this. This project was dumped into my lap cause I know more excel than the others in my office. As you can see I know a lot less than you all.

There will be times when I will need to change more than one character. So how do I change the =REPLACE to include two seperate characters.

Example:

WAKU260B needs to change to WHKC260B.

That is part one.

Part II

Same example as above except:

WAKU260A needs to change to WAKS260A
WAKU260B needs to change to WHKC260B

Basically if the text ends in "A" then it needs to change only the 4th character to "S". If it data ends in "B" it needs to change both the "A" to "H" and the "U" to "C".

There is one more part that I know of for this Spreadsheet. However, I need to meet with an engineer to help make certain of the names and the conditions. It will have character changes based on old gauge data being replaced by new gauge data. Once I have more info I will post it, but it will not be before tomorrow.

[mdmackillop]

Here's a User Defined Function to do it. You should be able to modify this for similar items.

Function Convert(Data As String)
Select Case Right(Data, 1)
Case "A"
Data = Application.WorksheetFunction.Replace(Data, 4, 1, "S")
Case "B"
Data = Application.WorksheetFunction.Replace(Data, 2, 1, "H")
Data = Application.WorksheetFunction.Replace(Data, 4, 1, "C")
End Select
Convert = Data
End Function

[Ken Puls]

Interesting, Malcolm...

I've always used Substitute over Replace. Do you see any advantages to Replace? (Other than it is less typing?)

[mdmackillop]

SUBSTITUTE

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string

Hi Ken,
The OP needed characters in a specific position replaced, not each occurence of a letter. Although there was only one occurence in the supplied data, it might not always be the case.
Regards
Malcolm

[rcbricker]

With the Substitute command, you are saying that it will replace "C" with "T" if it is found in that text character position?

[rcbricker]

the =Convert Command is working really well. However there are instances in which a M is the last text character. These are obsolete and need to be removed from the list. Can we Move these out of the data and over to columns R - W?

There is another instance in which "C" is the final character and it will have a change also, but we are waiting on confirmation as to what changes this will cause, I will let you know what those will be.

[rcbricker]

We have a new one.

We have some strings that will change based on the fourth character in the string. If the fourth character is not an ALPHA character then there will be no change. If it is an ALPHA and the character is anything except "J" or "X" then the first character in the string changes to "H". If the fourth character is a "J" then there is no change, if it is a "X" then it needs to change to a "J".

Any questions?

[Ken Puls]

Hi there,

Do a Select Case using the Mid function to isolate the fourth character. Then depending on the case results, use the Replace function.

(Malcolm, good point on Replace.)

rcbricker, Substitute will replace any C with T, for example. Replace gets a specific character.

:)

[rcbricker]

Hi Ken,

Thanks for getting involved. I don't know the select case or mid function commands (I suck at this stuff so far). Any help with it?

Thanks for explaining the substitute command sounds exactly like the find/replace that exists in the edit menu.

[mdmackillop]

Select Case is demonstated in Post #7 . Mid is similar to Right, check it in the Help files.

[rcbricker]

Ok given that there are a number of criteria that results in different changes to either the first or fourth character, I am wondering if a IF statement would be best. I tried writing one but to no avail.

It looks something like this:

=IF(H6(MID(J,4,1),AND(IF(H6(MID(X,4,1),REPLACE(H6,4,1,"J"),REPLACE(H6,1,1,"H")))))

I am trying to get this to look at the fourth character in the target cell. If the fourth character is a "J" or "x" then the fourth character needs to end up being a "J". If the fourth character is not a "J" or "X" then the first character needs to be a "H". Finally if the fourth character is numeric then there should be no change.

:bug:

[mdmackillop]

Whether a formula, UDF or macro is best depends upon your data. Is this to be applied to one cell or many. How do you want the results presented?

[rcbricker]

DAHJ043 needs to remain the same (fourth is already a J)
DAHX045 needs to change to DAHJ045 (fourth is an X and needs to be J)
DAH1087 needs to be ignored (fourth is non-alpha)
DAHC784 needs to change to DAHH784 (fourth is alpha and is neither J or X and as such needs to be H)

The original is in one cell and the new (or same depending on above criteria) needs to be in the cell directly to the right of it.

[mdmackillop]

How many cells are you dealing with?

[Ken Puls]

Based on the info in post 18

Function ConvertFourth(Data As String) As String
Select Case Mid(Data, 4, 1)
Case "X"
'Replace X with J
ConvertFourth = Application.WorksheetFunction.Replace(Data, 4, 1, "J")
Case "J"
'Data is fine
ConvertFourth = Data
Case Else
If IsNumeric(Mid(Data, 4, 1)) Then
'Numeric so okay
ConvertFourth = Data
Else
'Alpha, but not an X or J
ConvertFourth = Application.WorksheetFunction.Replace(Data, 4, 1, "H")
End If
End Select
End Function

[johnske]

DAHJ043 needs to remain the same (fourth is already a J)
DAHX045 needs to change to DAHJ045 (fourth is an X and needs to be J)
DAH1087 needs to be ignored (fourth is non-alpha)
DAHC784 needs to change to DAHH784 (fourth is alpha and is neither J or X and as such needs to be H)

The original is in one cell and the new (or same depending on above criteria) needs to be in the cell directly to the right of it.Assuming your data is in Column A (change A to suit) EDIT Missed a spec. :)

Sub Convert()
Dim Cell As Range
For Each Cell In Range("A1", Range("A" & Rows.Count).End(xlUp))
If Cell Like "???X*" Then
Cell.Offset(0, 1) = Application.Replace(Cell, 4, 1, "J")
ElseIf Not (Cell Like "???J*" Or Cell Like "???#*") Then
Cell.Offset(0, 1) = Application.Replace(Cell, 4, 1, "H")
End If
Next
End Sub

[rcbricker]

This worked great. This project is moving right along. I am going to have a more complex one after the guy who can answer questions gets back from being sick. It will be changes based on the gauge of the metal. We have moved from 12 gauges down to three and the new part number needs to be generated for some 15000 parts. Once I know the criteria I will come back and get you all to help me with this.

BTW you guys are lifesavers and absolutely make up the best excel help website I have ever used. Thank you so much for your time and help.

[Ken Puls]

FYI, guys...

Convert is a native function in Excel 2007. This is actually the reason that I changed the name on my variant of the code. :)

[rcbricker]

I tried converting the CONVERTFOURTH VBA to a new CONVERT function. When i enter it into the cell it hangs as =CONVERTFIRST(cell). Looks like text.

here is the code:


Function ConvertFirst(Data As String) As String
Select Case Left(Data, 4, 1)
Case "D"
'Replace D with H & 4th Character with H
ConvertFirst = Application.WorksheetFunction.Replace(Data, 1, 1, "H")
ConvertFirst = Application.WorksheetFunction.Replace(Data, 4, 1, "H")
End If
End Select
End Function


Any ideas why it will not change from the =CONVERTFIRST to the formula?

[Bob Phillips]

Function ConvertFirst(Data As String) As String
Select Case Left(Data, 1)
Case "D"
'Replace D with H & 4th Character with H
Data = "H" & Right(Data, Len(Data) - 1)
ConvertFirst = Left(Data, 3) & "H" & Right(Data, Len(Data) - 4)
End Select
End Function

[rcbricker]

Thanks XLD!