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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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!

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

Did you try the following?

Please Login or Register  to view this content.

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)))

**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?

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

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

Formula:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

	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:

Please Login or Register  to view this content.

	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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

or even this

Formula:

Please Login or Register  to view this content.

Removing leading AND trailing apostrophes from a text string

LinkBack

Thread Tools

Rate This Thread

Display

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