How do I add a "-" in two different character positions within a text string?
For example, the current text string is WF121202 and I need to change it to WF121-2-02 for numerous rows of data.
How do I add a "-" in two different character positions within a text string?
For example, the current text string is WF121202 and I need to change it to WF121-2-02 for numerous rows of data.
forti2ude95,
Welcome to the forum!
If all of the text strings are the same length (8 characters), you can use:
=LEFT(A1,5)&"-"&MID(A1,6,1)&"-"&RIGHT(A1,2)
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
If all of the cells follow the same length/structure, the following macro will work without the need for a helper column. (Note, the macro is set to apply to any selected cells)
![]()
Sub AddStuffAndStuff() Dim R As Range Dim arr(1 To 3) As String Set R = Selection For Each itm In R arr(1) = Left(itm, 5) arr(2) = Mid(itm, 6, 1) arr(3) = Right(itm, 2) itm.Value = arr(1) & "-" & arr(2) & "-" & arr(3) Next itm End Sub
![]()
strOld = "WF121202" strNew = Left(strOld, 5) & "-" & Mid(strOld, 6, 1) & "-" & Right(strOld, 2)
Great, it works!! Thanks a bunch!!! Yhis has saved me a lot of time!
Regards,
forti2ude95
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks