vba to insert space in front of first character and then a space after the 12th

Could an expert provide me with VBA that will do the following please:

If the cell begins with ABC remove ABC then insert space in front of first character and then insert a space after the 12th character but keep all of the data in the cell.

If the cell does not begin with ABC then insert space in front of first character and then insert a space after the 12th character but keep all of the data in the cell.

i.e.

if the cell contains

ABCXS1426698194.001//CPN121780 change to [space]XS1426698194 .001//CPN121780

if the cell contains

XS1426698194.001//CPN121780 change to [space]XS1426698194 .001//CPN121780


Hope this is self explanitory

Thanks
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Where? Over the entire workbook, all spreadsheets? Over one column? Over a selection?
0
JagwarmanAuthor Commented:
Sorry good point. column 'N'
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please save your spreadsheet before running this code.

Sub UpdateColumn()
For introw = 1 To 65000
    If Cells(introw, 14) <> "" Then
        If Left(Cells(introw, 14), 3) = "ABC" Then
            Cells(introw, 14) = Mid(Cells(introw, 14), 4, 9999)
        End If
        Cells(introw, 14) = " " & Left(Cells(introw, 14), 12) & " " & Mid(Cells(introw, 14), 13, 9999)
    End If
Next
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Glen RichmondCustomer Reporting Programmer.Commented:
VBA is as follows (also example on how to use function as cell function attached)

Function ParseString(myString As String)
      'myString will = "ABCXS1426698194.001//CPN121780 change to [space]XS1426698194 .001//CPN121780"
      
      'optional triim if you want to?
      myString = Trim(myString)
      
      'take off the ABC
      If Left(myString, 3) = "ABC" Then
            myString = Mid(myString, 4)
            
      End If
      
      'now add the spaces
      myString = " " & myString & " "
      
      ParseString = myString
      
End Function

Open in new window

Example.xlsm
0
Glen RichmondCustomer Reporting Programmer.Commented:
Phillip Burton
Excel no longer limited to 65k rows..
wouldnt usedrows be a better option?

or my solution which acts as a user defined in cell function.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I know Excel is no longer limited to 65,536 rows. But I doubt that Jagwarman's data goes that far. And if it does, he can modify it if he wishes.
0
Glen RichmondCustomer Reporting Programmer.Commented:
sorry but i hate built in limitation, or hard coding where it can be avoided.
0
JagwarmanAuthor Commented:
Glen,

I am not familiar with functions, apologies for being dumb but how do I get yours to run
0
Glen RichmondCustomer Reporting Programmer.Commented:
if you want to include into your workbook, include the modTest into your VBAProject.
(export from example, import into yours)
then in the colum cells add the =parsestring(<col&cell ref>)
as you would do any normal in cell function.

Attached a image to try and help..
example.png
0
aikimarkCommented:
I don't see the 12th character space in your two examples.
If the ABC is removed, does the 12th character start with the new first character or the original first character.  Or does the 12th character start with the pre-pended space character?
0
Glen RichmondCustomer Reporting Programmer.Commented:
sorry missed that, you want where you have put [space] to be an actual space?
12th chr from the left once the ABC has been removed and leading space added?
Ill make a example you can move where the 12th chr is my editing the code..
0
Glen RichmondCustomer Reporting Programmer.Commented:
example with adding in 12th pos chr..

you should have all you need in that function now to pull that string appart as much as you want :)
0
krishnakrkcCommented:
Hi

Select the range and run this macro.

Option Explicit

Sub kTest()
    
    Dim x   As String
    
    With Selection
        x = .Address
        .Value = Evaluate("=IF(LEFT(" & x & ",3)=""abc"","" ""&REPLACE(MID(" & x & ",4,LEN(" & x & ")),12,1,"" ""),"" ""&REPLACE(" & x & ",12,1,"" ""))")
    End With
    
End Sub

Open in new window


Kris
0
Glen RichmondCustomer Reporting Programmer.Commented:
sorry no idea whay second example didnt attach...
here it is (i hope)
Example2.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.