Removing leading AND trailing apostrophes from a text string

**kazaly** · 09-24-2017, 02:22 AM

Hi all and I'm hoping there's someone here who's had this problem.

I regularly receive extracts from a database in CSV format.
Until recently there were no real issues, just a few simple reformats and I could roll the data into my spreadsheet.
Then someone at the other end decided to change the extraction algorithm and the CSV now encapsulates text strings within apostrophes:

' Australian Women's Weekly Children's Birthday Cakes '

My question is; how do I get rid of the 2 outer apostrophes while leaving the 2 inner ones in place?
Output needs to become:

Australian Women's Weekly Children's Birthday Cakes

I match these title strings with a stock list and unfortunately stock codes are not an option due to CSV report field limitations.
As I have several years of existing data it would be a massive undertaking to reconcile these two different data types.
All I really want to do is "clean up" the CSV data before pasting it into the master xlsx spreadsheet.

**sandy666** · 09-24-2017, 02:29 AM

Use PowerQuery and remove first 'and (space) and last (space) and '

**cbatrody** · 09-24-2017, 03:01 AM

OR,

Try the following:

=SUBSTITUTE(SUBSTITUTE(A1," ' ",""),"' ","")

**sandy666** · 09-24-2017, 03:06 AM

or

Formula:

1) =REPLACE(REPLAC E(A2,1,2,""),LEN(REPLAC E(A2,1,2,""))-2,2,"")

2) =MID(A2,3,LEN(A2)-4)

note: there is a space before E( because of sucuri firewall

**AliGW** · 09-24-2017, 05:46 AM

You can do this with two passes of Find & Replace if the spaces are there as you show in your example.

**AlKey** · 09-24-2017, 09:11 AM

Try this
in B1

Formula:

=TRIM(MID(A1,2,LEN(TRIM(A1))-1))

v	A	B
1	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes

**kazaly** · 09-26-2017, 02:38 AM

Thanks for all your help but I've now discovered that the effect is not uniform ie sometimes a lead or trailing apostrophe is NOT included.
I guess I'm going to have to look at designing a macro instead as any operation will now be conditonal.
OMG I so loathe sloppy programmers!

**sandy666** · 09-26-2017, 03:10 AM

Originally Posted by kazaly

Thanks for all your help but I've now discovered that the effect is not uniform ie sometimes a lead or trailing apostrophe is NOT included.

could you give us excel file with more than one, different examples?

**cbatrody** · 09-26-2017, 02:57 AM

Did you try the following?

=TRIM(SUBSTITUTE(SUBSTITUTE(A1," ' ",""),"' ",""))

It works even if there are random 's in the beginning or at the end.

See the attached file.

**Glenn Kennedy** · 09-26-2017, 03:00 AM

Alkey will come up with something one tenth the length of this one.... but try:

=TRIM(IF(RIGHT(IF(LEFT(A1,1)="'",MID(A1,2,255),A1),1)="'",LEFT(IF(LEFT(A1,1)="'",MID(A1,2,255),A1),LEN(IF(LEFT(A1,1)="'",MID(A1,2,255),A1))-1),IF(LEFT(A1,1)="'",MID(A1,2,255),A1)))

**AlKey** · 09-26-2017, 11:57 AM

This is actually a bit of headache... but here is something that might work.

Formula:

=TRIM(LEFT(A1,LEN(TRIM(A1))+IF(LEN(SUBSTITUTE(SUBSTITUTE(RIGHT(TRIM(A1),2)," ",""),"'",""))=1,-1,0)))

v	A	B
1	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
2	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
3	Australian Women's Weekly Children's Birthday Cakes'	Australian Women's Weekly Children's Birthday Cakes
4	Australian Women's Weekly Children's Birthday Cakes	Australian Women's Weekly Children's Birthday Cakes

**FlameRetired** · 09-26-2017, 01:09 PM

Do you suppose this will do it?

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"'s ","^^"),"'",""),"^^","'s "))

	A	B
1	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
2	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
3	Australian Women's Weekly Children's Birthday Cakes'	Australian Women's Weekly Children's Birthday Cakes
4	Australian Women's Weekly Children's Birthday Cakes	Australian Women's Weekly Children's Birthday Cakes

**AlKey** · 09-26-2017, 01:21 PM

Originally Posted by FlameRetired

Do you suppose this will do it?

Formula:

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"'s ","^^"),"'",""),"^^","'s "))

	A	B
1	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
2	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
3	Australian Women's Weekly Children's Birthday Cakes'	Australian Women's Weekly Children's Birthday Cakes
4	Australian Women's Weekly Children's Birthday Cakes	Australian Women's Weekly Children's Birthday Cakes

That would way be too easy

You can't depend on a part of one word ('s"). Otherwise you wold have to make changes to the formula for each sentence.

**FlameRetired** · 09-26-2017, 01:40 PM

Originally Posted by AlKey

That would way be too easy

You can't depend on a part of one word ('s"). Otherwise you wold have to make changes to the formula for each sentence.

AlKey I understand, and was afraid of that even if it's "'s " and not "'s". There are others. For example can't, you're or worse plural possessives.

Still I was hoping to open another approach.

**AlKey** · 09-26-2017, 02:05 PM

I think this one should do better

Formula:

=IF(CODE(RIGHT(TRIM(SUBSTITUTE(A1," '","'"))))=39,LEFT(TRIM(SUBSTITUTE(A1," '","'")),LEN(TRIM(SUBSTITUTE(A1," '","'")))-1),TRIM(A1))

v	A	B
1	Australian Women's Weekly Children's Birthday Cakes'	Australian Women's Weekly Children's Birthday Cakes
2	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
3	Australian Women's Weekly Children's Birthday Cakes '	Australian Women's Weekly Children's Birthday Cakes
4	Australian Women's Weekly Children's Birthday Cakes	Australian Women's Weekly Children's Birthday Cakes

A little bit shorter

Formula:

=IF(CODE(RIGHT(TRIM(SUBSTITUTE(A1," '","'"))))=39,LEFT(TRIM(SUBSTITUTE(A1," '","'")),LEN(TRIM(A1))-1),TRIM(A1))

or even this

Formula:

=IF(RIGHT(TRIM(A1))="'",LEFT(TRIM(A1),LEN(TRIM(A1))-1),TRIM(A1))

Removing leading AND trailing apostrophes from a text string

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Re: Removing leading AND trailing apostrophes from a text string

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to get rid of leading apostrophes in text cells

Highlight leading and trailing spaces of text within cells

[SOLVED] How to remove all leading and trailing colons (;) from a text?

Removing leading and trailing / ,

Delete Trailing Spaces without Removing leading zeros WITHOUT TRIM

[SOLVED] Removing leading and trailing spaces

[SOLVED] How to save Excel string data without the leading/trailing DQ (")s

Bookmarks

Bookmarks

Posting Permissions