Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bring back lowercase values
Anyone got a solution to return only lowercase vaues in a string. Excluding
where an x is on its own. Example1 Abelia x grandiflora Francis Mason Returns grandiflora Example2 Abies balsamea f. hudsonia Returns balsamea f. hudsonia Example3 Abutilon vitifolium var. Album Returns vitifolium var. Example4 Abies nordmanniana Golden Spreader Returns nordmanniana Much appreciate if you can help. Pat |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bring back lowercase values
Hi Pat
Haven't see you round for a while. You could use DataText to Columnsuse Space as delimiter to break each cell into separate words. Then in a column to the right of the parsed data (i used column H) enter =IF(A1="","",IF(A1="x","",IF(EXACT(LOWER(A1),A1),A 1,""))) Copy across as far as required In the first available column after this (I used column O) =TRIM(H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1) -- Regards Roger Govier "Pat" wrote in message ... Anyone got a solution to return only lowercase vaues in a string. Excluding where an x is on its own. Example1 Abelia x grandiflora Francis Mason Returns grandiflora Example2 Abies balsamea f. hudsonia Returns balsamea f. hudsonia Example3 Abutilon vitifolium var. Album Returns vitifolium var. Example4 Abies nordmanniana Golden Spreader Returns nordmanniana Much appreciate if you can help. Pat |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bring back lowercase values
Hello Roger,
Good to hear form you, my focus has been directed elsewhere of late. Your solution gave me the result i wanted, many thanks. Pat "Roger Govier" wrote in message ... Hi Pat Haven't see you round for a while. You could use DataText to Columnsuse Space as delimiter to break each cell into separate words. Then in a column to the right of the parsed data (i used column H) enter =IF(A1="","",IF(A1="x","",IF(EXACT(LOWER(A1),A1),A 1,""))) Copy across as far as required In the first available column after this (I used column O) =TRIM(H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1) -- Regards Roger Govier "Pat" wrote in message ... Anyone got a solution to return only lowercase vaues in a string. Excluding where an x is on its own. Example1 Abelia x grandiflora Francis Mason Returns grandiflora Example2 Abies balsamea f. hudsonia Returns balsamea f. hudsonia Example3 Abutilon vitifolium var. Album Returns vitifolium var. Example4 Abies nordmanniana Golden Spreader Returns nordmanniana Much appreciate if you can help. Pat |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Bring back lowercase values
Hi Pat
You're more than welcome - as always. -- Regards Roger Govier "Pat" wrote in message ... Hello Roger, Good to hear form you, my focus has been directed elsewhere of late. Your solution gave me the result i wanted, many thanks. Pat "Roger Govier" wrote in message ... Hi Pat Haven't see you round for a while. You could use DataText to Columnsuse Space as delimiter to break each cell into separate words. Then in a column to the right of the parsed data (i used column H) enter =IF(A1="","",IF(A1="x","",IF(EXACT(LOWER(A1),A1),A 1,""))) Copy across as far as required In the first available column after this (I used column O) =TRIM(H1&" "&I1&" "&J1&" "&K1&" "&L1&" "&M1&" "&N1) -- Regards Roger Govier "Pat" wrote in message ... Anyone got a solution to return only lowercase vaues in a string. Excluding where an x is on its own. Example1 Abelia x grandiflora Francis Mason Returns grandiflora Example2 Abies balsamea f. hudsonia Returns balsamea f. hudsonia Example3 Abutilon vitifolium var. Album Returns vitifolium var. Example4 Abies nordmanniana Golden Spreader Returns nordmanniana Much appreciate if you can help. Pat |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a specific number to more general values in a table. | Excel Worksheet Functions | |||
values not appearing in autofilter list | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |