Skip to main content

Hi all

Is there a way to delete the certain matching characters that might appear in a column? For example I have a column of data (800+ cells) of company names, and 90% of them are prefixed with a 'number' (that run between 1 and 9), followed by a 'period', followed by a 'space'. For example "1. Some Company Name" or "8. Another Company Name".

10% of the data in the column may not have any numbers or spaces, OR some of the company names may not have a 'space' after the 'period' for example "7.Yet Another Company Name" or "And Another Company Name".Amy advice would be greatly appreciated.

Stephen

 

Hi,

Use SUBSTITUTE to remove some certain characters pattern

Use REPLACE to remove characters at some certain place in string

In your case, you need to remove 'from start (position number 1) to period (located by FIND)'

 

REPLACE({field}, 1, FIND('.',{field}), '')

 

to remove possible space after period, wrap it all in TRIM:

 

TRIM(
REPLACE({field}, 1, FIND('.',{field}), '')
)

 

 

The formula for the "Actual Company Name" field is

REGEX_REPLACE({Listed Company Name},"[0-9](. )|[0-9](.)","")

Hi,

Use SUBSTITUTE to remove some certain characters pattern

Use REPLACE to remove characters at some certain place in string

In your case, you need to remove 'from start (position number 1) to period (located by FIND)'

 

REPLACE({field}, 1, FIND('.',{field}), '')

 

to remove possible space after period, wrap it all in TRIM:

 

TRIM(
REPLACE({field}, 1, FIND('.',{field}), '')
)

 

 


Many thanks @Alexey_Gusev ! Amazing worked a treat. 

 

The formula for the "Actual Company Name" field is

REGEX_REPLACE({Listed Company Name},"[0-9](. )|[0-9](.)","")


Brilliant @Ron_Daniel. A nice elegant solution. 'Many' thanks.

Reply


Terms & Conditions