Delete first matching characters in a column

[Stephen]

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

[Alexey_Gusev · Accepted solution]

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}), '')
)

---

[Ron_Daniel · Accepted solution]

The formula for the "Actual Company Name" field is

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

[Stephen]

Many thanks @Alexey_Gusev ! Amazing worked a treat. 

[Stephen]

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