Help

Extracting text from a field unless a character is a space

Topic Labels: Formulas
925 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Suurmeijer
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi there,

I’m looking for some help!
I have a formula in a cell that basically creates an automatic Project ID.
For this Project ID I use two fields:

  • A list of teams, which is formatted like “B – Brand” or “PM – Product Marketing”
  • Autonumber Field

I’m now using the following formula:
LEFT({Requesting Team:},2 ) & "-" & Autonumber

This formula is great, except for the fact that I am sometimes left with spaces:
The first Brand project becomes B -1
I would like for that to be B-1 (minus the space)

Any tips on how to achieve this?
Can I use LEN 2 unless the second character is a space, and then use LEN 1?

Thanks in advance!

3 Replies 3

Instead of setting 2 on LEFT, find the - position with FIND().

Paul_Suurmeijer
5 - Automation Enthusiast
5 - Automation Enthusiast

@Elias_Gomez_Sainz Thank you!
I got it in a better shape, but I’m still trying to figure out how to remove the space:

LEFT({Requesting Team:},FIND("–",{Column:})) & Autonumber

I could rename my Column entries from “B – Brand” to “B–Brand”, but I would love to keep the spaces in and just filter them out of the formula.

Oh yes, I didn’t realize that. Just find " -" (space + hyphen).