Solved

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

Posted on 2014-11-04
14
243 Views
Last Modified: 2014-11-06
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
0
Comment
Question by:Jagwarman
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40421641
Where? Over the entire workbook, all spreadsheets? Over one column? Over a selection?
0
 

Author Comment

by:Jagwarman
ID: 40421662
Sorry good point. column 'N'
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 300 total points
ID: 40421684
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421711
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421733
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40421750
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421760
sorry but i hate built in limitation, or hard coding where it can be avoided.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Jagwarman
ID: 40421768
Glen,

I am not familiar with functions, apologies for being dumb but how do I get yours to run
0
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421811
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
 
LVL 45

Expert Comment

by:aikimark
ID: 40421828
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421837
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421847
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
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 200 total points
ID: 40421892
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
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40421913
sorry no idea whay second example didnt attach...
here it is (i hope)
Example2.xlsm
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now