Insert single quote symbol

  • Thread starter Thread starter Gotroots
  • Start date Start date
G

Gotroots

I have a list of plant names that need a single quote inserted at the
appropriate location as the examples show.

Weigela Snowflake
Weigela ‘Snowflake’

Wisteria sinensis Caroline
Wisteria sinensis ‘Caroline’

Vinca minor Alba Variegata
Vinca minor ‘Alba Variegata’

Viburnum x rhytidophylloides Willowwood
Viburnum x rhytidophylloides ‘Willowwood’

Viburnum plicatum f. tomentosum Lanarth
Viburnum plicatum f. tomentosum ‘Lanarth’

Veronica austriaca subsp. teucrium Shirley Blue
Veronica austriaca subsp. teucrium ‘Shirley Blue’

Symphoricarpos x doorenbosii Mother of Pearl
Symphoricarpos x doorenbosii ‘Mother of Pearl’

Syringa vulgaris Andenken an Ludwig Späth
Syringa vulgaris ‘Andenken an Ludwig Späth’

Saxifraga umbrosa var. primuloides Elliott's Variety
Saxifraga umbrosa var. primuloides 'Elliott's Variety'


Much apprieciate any help given
 
Each item of data is a set of words
The words are separated by a space.

In some of your examples, you need the last word in the set to be
encapsulated by single quotes. In other examples, you need the last two
words to be encapsulated.

How can the formula determine which result to produce??
 
Describe how a computer program would know why this is correct:

Symphoricarpos x doorenbosii 'Mother of Pearl'


instead of:

Symphoricarpos x doorenbosii Mother 'of Pearl'


or this:

Symphoricarpos x doorenbosii Mother of 'Pearl'
 
To make it a little shorter, let's use a helper column.

Say the list is in Column A.
In B1, enter this formula:

=MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"},
A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZ",2))-1

in C1, enter this formula:

=LEFT(A1,B1)&"'"&RIGHT(A1,LEN(A1)-B1)&"'"

Select *both* B1 and C1, and copy that 2 cell selection down as far as
needed.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================



Are you saying there is no formula however complex will determine this?
 
Ok, point taken.

How about then first separating the text that needs to be enclosed in single
quotes.

Apart from the initial capitalization of a plant name where a capitalization
begins would be the point where the separation would begin.

Here is an example

Symphoricarpos x doorenbosii Mother of Pearl

Mother of Pearl

Once separated Mother of Pearl (the value) would perhaps then be more easily
enclosed in single quotes.
 
That helps. Try this array formula (commit with CTRL+SHIFT+ENTER):

=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))>64)*
(CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"&
MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))>64)*
(CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'"

Probably not optimized very well, but it seems to work.
 
A cleverly constructed formula if i may say so.

It works beautifully, except a little tweeking is now called for.

Plants have there own naming convention hence the varied examples shown
earlier.

The formula will return N/A when faced with a value such as

Arundo donax var. versicolor

The above example does not have any capitalization (apart from the first
letter) in its name.

I need the formula to return these values also even though there is no
single quotes to insert into the name.
 
Brute force:

=IF(ISNA(MATCH(1,(CODE(MID(A1,ROW($2:$99),1))>64)*
(CODE(MID(A1,ROW($2:$99),1))<91),0)),A1,
LEFT(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))>64)*
(CODE(MID(A1,ROW($2:$99),1))<91),0))&"'"&
MID(A1,MATCH(1,(CODE(MID(A1,ROW($2:$99),1))>64)*
(CODE(MID(A1,ROW($2:$99),1))<91),0)+1,LEN(A1))&"'")
 
Not quite there yet.

Although the formula returns non single quote values it now returns N/A for
any values that contain single quotes.

I need both non and single quote values to be returned.
 
Back
Top