(in)definite article and leading/trailing space remover needed
 

In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

be sure to back up your data in case this is not what you want. any leading
words to be deleted, just add to Array1 (in quotes, separated by comma).

Sub Test()
Dim Array1()

On Error Resume Next
Array1 = Array("The", "A", "An")

For Each x In Selection
x.Value = Trim(x.Value)
For Each y In Array1
If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _
And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If
Next y
Next x

End Sub


"KHashmi316" wrote:


In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

when you paste the code into a module, make sure this line appears on one
line. when i posted it got wrapped to the next line (and it's not supposed
to be).

If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _


"KHashmi316" wrote:


In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Why not do it as a UDF? You can then enter it in your sheet as a function,
or use it as a macro as shown below.

Option Private Module
Option Explicit
Option Compare Text

Public Function ParseIndefinites(vValue As Variant) As Variant
Dim vIndefs As Variant
Dim vTest As Variant

vIndefs = Array("The ", "An ", "A ")
vValue = Trim(vValue)

For Each vTest In vIndefs

If InStr(vValue, vTest) = 1 Then

ParseIndefinites = Mid(vValue, Len(vTest) + 1) & ", " & vTest
Exit Function

End If

Next vTest
ParseIndefinites = vValue
End Function

Sub Parse()
Dim rngCell As Range
Dim rngTest As Range

For Each rngCell In Intersect(Sheets(1).Columns(1).EntireColumn,
Sheets(1).UsedRange).Cells
If Not IsEmpty(rngCell.Value) Then rngCell.Value =
ParseIndefinites(rngCell.Value)
Next rngCell
End Sub


Robin Hammond
www.enhanceddatasystems.com

"KHashmi316" wrote
in message ...

In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile:
http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

The following page contains a macro (trimall) that takes care of
leading/trailing space issue:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Still need solution for (in)definite articles.


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

JMB Wrote:
when you paste the code into a module, make sure this line appears on
one
line. when i posted it got wrapped to the next line (and it's not
supposed
to be).

If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _


Thx for helping out.

I tried to run this but keep running into 'Variable not defined' error
for "x" in this line:

For Each x In Selection

Any clues?


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Robin,

Thx for your reply.

I tried to enter your suggestion into the VBA editor a number of ways,
but could not get it to work. No error messages, no action of any kind
AFAICS. I probably misunderstood your instructions.

What did you have in mind if one wishes to simply use this as a macro
(and not as a function)?

Robin Hammond Wrote:
Why not do it as a UDF? You can then enter it in your sheet as a
function,
or use it as a macro as shown below.

Option Private Module
Option Explicit
Option Compare Text

Public Function ParseIndefinites(vValue As Variant) As Variant
Dim vIndefs As Variant
Dim vTest As Variant

vIndefs = Array("The ", "An ", "A ")
vValue = Trim(vValue)

For Each vTest In vIndefs

If InStr(vValue, vTest) = 1 Then

ParseIndefinites = Mid(vValue, Len(vTest) + 1) & ", " & vTest
Exit Function

End If

Next vTest
ParseIndefinites = vValue
End Function

Sub Parse()
Dim rngCell As Range
Dim rngTest As Range

For Each rngCell In Intersect(Sheets(1).Columns(1).EntireColumn,
Sheets(1).UsedRange).Cells
If Not IsEmpty(rngCell.Value) Then rngCell.Value =
ParseIndefinites(rngCell.Value)
Next rngCell
End Sub


Robin Hammond
www.enhanceddatasystems.com

"KHashmi316"
wrote
in message
...

In order to better alphabetize a books database, I would like to use
a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an
(in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after
selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316



------------------------------------------------------------------------
KHashmi316's Profile:
http://www.excelforum.com/member.php...o&userid=10439
View this thread:
http://www.excelforum.com/showthread...hreadid=378854



--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

True, but Excel worksheet functions could also be used and they'd probably
run faster than either the macro or the UDF.

Assuming the data is in cell D3

=IF(ISERROR(MATCH(LEFT(TRIM(D3),SEARCH("
",TRIM(D3),1)-1),{"A","An","The"},0)),D3,RIGHT(TRIM(D3),LEN(D3)-SEARCH("
",TRIM(D3),1)))

"Robin Hammond" wrote:

Why not do it as a UDF? You can then enter it in your sheet as a function,
or use it as a macro as shown below.

Option Private Module
Option Explicit
Option Compare Text

Public Function ParseIndefinites(vValue As Variant) As Variant
Dim vIndefs As Variant
Dim vTest As Variant

vIndefs = Array("The ", "An ", "A ")
vValue = Trim(vValue)

For Each vTest In vIndefs

If InStr(vValue, vTest) = 1 Then

ParseIndefinites = Mid(vValue, Len(vTest) + 1) & ", " & vTest
Exit Function

End If

Next vTest
ParseIndefinites = vValue
End Function

Sub Parse()
Dim rngCell As Range
Dim rngTest As Range

For Each rngCell In Intersect(Sheets(1).Columns(1).EntireColumn,
Sheets(1).UsedRange).Cells
If Not IsEmpty(rngCell.Value) Then rngCell.Value =
ParseIndefinites(rngCell.Value)
Next rngCell
End Sub


Robin Hammond
www.enhanceddatasystems.com

"KHashmi316" wrote
in message ...

In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile:
http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

you may have Option Explicit in your module (requiring variables to be
declared)
under the existing Dim statement add

Dim x as object




"KHashmi316" wrote:


JMB Wrote:
when you paste the code into a module, make sure this line appears on
one
line. when i posted it got wrapped to the next line (and it's not
supposed
to be).

If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _


Thx for helping out.

I tried to run this but keep running into 'Variable not defined' error
for "x" in this line:

For Each x In Selection

Any clues?


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Also - if you use the macro - you should change the For Each line to

For Each x In Intersect(Selection, Selection.Parent.UsedRange)

that way - it doesn't waste time looping through cells that are not used
anyway.



"KHashmi316" wrote:


JMB Wrote:
when you paste the code into a module, make sure this line appears on
one
line. when i posted it got wrapped to the next line (and it's not
supposed
to be).

If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _


Thx for helping out.

I tried to run this but keep running into 'Variable not defined' error
for "x" in this line:

For Each x In Selection

Any clues?


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

you may have Option Explicit in your module (requiring variables to be
declared)
under the existing Dim statement add

Dim x as object


...and also ditto for y, I assume (which I did). But now, I get the
following compile error ...

"For Each control variable on arrays must be a Variant"

...with 'y' highlighted in the follwoing line:

For Each y In Array1

So, I declared 'y' as a Variant. Next, I ran into a compile error due
to End If, per your orig code, so I removed "End If".

It runs, but ONLY removes leading "The", not "A" or 'An".

Here's the whole macro as of the latest edit:

Sub Test()

Dim x As Object
Dim y
Dim Array1()


On Error Resume Next
Array1 = Array("The", "A", "An", "a", "an", "the")

For Each x In Intersect(Selection, Selection.Parent.UsedRange)
x.Value = Trim(x.Value)
For Each y In Array1
If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _
And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then x.Value =
Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))


Exit For


Next y
Next x


End Sub


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

no need for macros: you could do it with formulas.
copy down next to your list et voila..

SEARCH is very flexible since it accepts arrays.
Note it's case insensitive


=TRIM(
IF(
ISNA(MATCH(1,SEARCH({"The ";"A ";"An "},A1),0)),
A1,
MID(A1,LOOKUP(1,SEARCH({"The ";"A ";"An "},A1),{4;2;3})+1,9999)
)
)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


KHashmi316 wrote :


In order to better alphabetize a books database, I would like to use a
macro that checks for and deletes all titles that BEGIN with "The",
"An", "A", etc. IOW, if the first word in the cell is an (in)definite
article.

It would help if this SAME macro did a pre-scan for leading and
trailing spaces, and deleted them accordingly.

Finally, this macro would be most convenient if worked after selecting
a column.

Thx for any input you can provide on creating such a macro!

(in)definite article and leading/trailing space remover needed
 

keepITcool Wrote:
no need for macros: you could do it with formulas.
copy down next to your list et voila..



No. Formulas are not useful in my particular situation for the very
reason ("copy down next to your list") you note.


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Hello KHashmi316,

Copy and Paste this macro code into a standard VBA Module. To use it
first select the cells or columns you want. Press ALT+F8 to bring u
the Macro List and select "RemoveArticles" and click RUN. To use thi
macro in code simply select the range first and call the macro.

MACRO CODE


Code
-------------------
Public Sub RemoveArticles()

Dim sLen As Integer
Dim iSpace As Integer
Dim Article As String
Dim TitleText As String

Dim C As Long
Dim R As Long

Dim StartCol As Long
Dim EndCol As Long
Dim StartRow As Long
Dim EndRow As Long

With Selection
StartCol = .Column
StartRow = .Row
EndCol = .Columns.Count - StartCol + 1
EndRow = .Rows.Count - StartRow + 1
End With

'Loop through the Selection
For C = StartCol To EndCol
For R = StartRow To EndRow
'Remove leading spaces from title
TitleText = LTrim(Cells(R, C).Value)
'Find the space that separates the first and second words
iSpace = InStr(1, TitleText, Chr$(vbKeySpace))
'Check if separating space is present
If iSpace 0 Then
'Isolate the first word
Article = LCase(Left(TitleText, iSpace - 1))
'Check if it is an article
If Article = "a" Or Article = "an" Or Article = "the" Then
'Get length of Title
sLen = Len(TitleText)
'Save Title without the article or leading and trailing spaces
Cells(R, C).Value = Trim(Right(TitleText, sLen - iSpace))
End If
Else
'Remove any leading or trailing spaces in the Title
Cells(R, C).Value = Trim(TitleText)
End If
Next R
Next C

Cells(StartRow, StartCol).Activate

End Su
-------------------

--
Leith Ros

-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=37885

(in)definite article and leading/trailing space remover needed
 

Leith Ross Wrote:


Copy and Paste this macro code into a standard VBA Module. To use it,
first select the cells or columns you want. Press ALT+F8 to bring up
the Macro List and select "RemoveArticles" and click RUN. To use this
macro in code simply select the range first and call the macro.


Hi, Leith:

I did as you instructed, but nothing happened. Unfortunately, I don't
know enough VBA to troubleshoot. If it helps, I'm using Excel 2002.

Thx for any further light you can shed.


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Hello KHashmi316,

I should have included this with code...

TO ADD A VBA MODULE:
1) Copy all the Macro Code by selecting it and then pressing *CTRL and
C*
2) Open the Workbook
3) Press *ALT and F11*, this opens the VBA Editor
4) Bring up the Insert Menu by pressin *ALT and I*
5) Insert a new Module into the Project by pressing the Letter *M*
6) Paste the code into the Module by pressing *CTRL and V*
7) Press *CTRL and S* to Save the Macro

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Backup your original macro before trying my macro.

I am not sure if this is what you are looking for.


Sub macro()
Dim r As Range
Set r = Selection
For Each c In r
c.Value = Trim(c.Value)
t = Split(c.Value, " ")
If (UBound(t) 0) Then
If (t(0) = "a" Or t(0) = "an" Or t(0) = "the") Then
c.Value = Trim(Mid(c.Value, Len(t(0)) + 1))
End If
End If
Next
End Sub


--
anilsolipuram
------------------------------------------------------------------------
anilsolipuram's Profile: http://www.excelforum.com/member.php...o&userid=16271
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

Leith Ross Wrote:
Hello KHashmi316,

I should have included this with code...

TO ADD A VBA MODULE
1) Copy all the Macro Code by selecting it and then pressing *CTRL an
C*
2) Open the Workbook
3) Press *ALT and F11*, this opens the VBA Editor
4) Bring up the Insert Menu by pressin *ALT and I*
5) Insert a new Module into the Project by pressing the Letter *M*
6) Paste the code into the Module by pressing *CTRL and V*
7) Press *CTRL and S* to Save the Macro

Sincerely,
Leith Ross

Hi, Leith:

I do know the *basics* of creating macros, so I'm familiar with th
procedure. IOW, already did all of the above. As far as the cod
itself, I can't tell you why it's not working -- for me, anyway

--
KHashmi31

-----------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...fo&userid=1043
View this thread: http://www.excelforum.com/showthread.php?threadid=37885

(in)definite article and leading/trailing space remover needed
 

Hello KHashmi316,

Have you set your System Security level to medium? If it is set to High
the Macro won't run.

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

anilsolipuram Wrote:
Backup your original macro before trying my macro.

I am not sure if this is what you are looking for.


Sub macro()
Dim r As Range
Set r = Selection
For Each c In r
c.Value = Trim(c.Value)
t = Split(c.Value, " ")
If (UBound(t) 0) Then
If (t(0) = "a" Or t(0) = "an" Or t(0) = "the") Then
c.Value = Trim(Mid(c.Value, Len(t(0)) + 1))
End If
End If
Next
End Sub

Hi, anilsolipuram:

Ran into compile error ("Variable not defined) on this line of code:

For Each c In

--
KHashmi31

-----------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...fo&userid=1043
View this thread: http://www.excelforum.com/showthread.php?threadid=37885

(in)definite article and leading/trailing space remover needed
 

Leith Ross Wrote:
Hello KHashmi316,

Have you set your System Security level to medium? If it is set to Hig
the Macro won't run.

Sincerely,
Leith Ross

Hi, Leith:

Security is at medium. I run dozens of macros on a daily basis, so
don't believe this is the issue -- not unless there is somethin
particular regarding *your* macro and security

--
KHashmi31

-----------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...fo&userid=1043
View this thread: http://www.excelforum.com/showthread.php?threadid=37885

(in)definite article and leading/trailing space remover needed
 

Hello KHashmi316,

The macro runs fine on my machine. Send me a copy of your Workbook and
let me look it over to find the problem. My e-mail is


Thanks,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

The End If has to be there. I think the following line may be the issue

And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If


If Then statements can appear on one line. If so, you don't need the End
If. If the IF/THEN is being treated as one line then the Exit For will be
outside of the IF statement, causing the macro to end after looping through
only the first element of Array1 (which is "The"). In this case, the Then
statement has two lines of code requiring the End If.

Make sure the code appears exactly as I've written it above. Also, you
don't need to include lowercase "a" "an" "the" in the array. For comparison
purposes the macro converts everything to uppercase (but doesn't change how
it appears in the spreadsheet).






"KHashmi316" wrote:


you may have Option Explicit in your module (requiring variables to be
declared)
under the existing Dim statement add

Dim x as object


...and also ditto for y, I assume (which I did). But now, I get the
following compile error ...

"For Each control variable on arrays must be a Variant"

...with 'y' highlighted in the follwoing line:

For Each y In Array1

So, I declared 'y' as a Variant. Next, I ran into a compile error due
to End If, per your orig code, so I removed "End If".

It runs, but ONLY removes leading "The", not "A" or 'An".

Here's the whole macro as of the latest edit:

Sub Test()

Dim x As Object
Dim y
Dim Array1()


On Error Resume Next
Array1 = Array("The", "A", "An", "a", "an", "the")

For Each x In Intersect(Selection, Selection.Parent.UsedRange)
x.Value = Trim(x.Value)
For Each y In Array1
If (UCase(Left(x.Value, InStr(1, x.Value, " ", vbTextCompare) - 1)) =
UCase(y)) _
And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then x.Value =
Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))


Exit For


Next y
Next x


End Sub


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

JMB Wrote:
The End If has to be there. I think the following line may be the
issue

And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If


If Then statements can appear on one line. If so, you don't need the
End
If. If the IF/THEN is being treated as one line then the Exit For will
be
outside of the IF statement, causing the macro to end after looping
through
only the first element of Array1 (which is "The"). In this case, the
Then
statement has two lines of code requiring the End If.

Make sure the code appears exactly as I've written it above. Also,
you
don't need to include lowercase "a" "an" "the" in the array. For
comparison
purposes the macro converts everything to uppercase (but doesn't change
how
it appears in the spreadsheet).


Hi, JMB:

Hoorah -- that worked! One more tweak I was thinking about was a way to
copy whatever (in)definite article the macro finds at the beginning of
the title, and concatenate it to the end of the title, such as:

The Scarlet Letter

to:

Scarlet Letter, The

By and large, however, article removing was my main concern. If you
have a quick suggestion, that's fine, but this new tweak is not that
critical.

Thx, again!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

I'm glad that worked out okay.

To put the article at the end find this line

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare))

and add & ", " & Y

so that you have

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare)) & ", " & Y



"KHashmi316" wrote:


JMB Wrote:
The End If has to be there. I think the following line may be the
issue

And (InStr(1, x.Value, " ", vbTextCompare) - 1 0) Then
x.Value = Trim(Replace(x.Value, y, "", 1, 1, vbTextCompare))
Exit For
End If


If Then statements can appear on one line. If so, you don't need the
End
If. If the IF/THEN is being treated as one line then the Exit For will
be
outside of the IF statement, causing the macro to end after looping
through
only the first element of Array1 (which is "The"). In this case, the
Then
statement has two lines of code requiring the End If.

Make sure the code appears exactly as I've written it above. Also,
you
don't need to include lowercase "a" "an" "the" in the array. For
comparison
purposes the macro converts everything to uppercase (but doesn't change
how
it appears in the spreadsheet).


Hi, JMB:

Hoorah -- that worked! One more tweak I was thinking about was a way to
copy whatever (in)definite article the macro finds at the beginning of
the title, and concatenate it to the end of the title, such as:

The Scarlet Letter

to:

Scarlet Letter, The

By and large, however, article removing was my main concern. If you
have a quick suggestion, that's fine, but this new tweak is not that
critical.

Thx, again!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854

(in)definite article and leading/trailing space remover needed
 

JMB Wrote:
I'm glad that worked out okay.

To put the article at the end find this line

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare))

and add & ", " & Y

so that you have

X.Value = Trim(Replace(X.Value, Y, "", 1, 1, vbTextCompare)) & ", " & Y

Hi, JMB:

Works great!

Thx again!


--
KHashmi316


------------------------------------------------------------------------
KHashmi316's Profile: http://www.excelforum.com/member.php...o&userid=10439
View this thread: http://www.excelforum.com/showthread...hreadid=378854