community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Formula to trim the first number

Asteroid

Hello,

 

I have a column with 6 and 7 digits numbers. I want to remove the first digit form 7 digits number and leave 6 untouched

 

DATE               EXPECTED RESULTS

940729              940729  

1020906            020906

940729              940729

1020906            020906

1020906            020906

940711              940711

 

 

Thanks

 

 

 

 

 

Bolide

Hi @EvansM, try this formula. ToNumber(IIF(Length(ToString([Date]))=6,[Date], Substring(ToString([Date]),1,6)))

Alteryx Certified Partner

Hi @EvansM 

 

REGEX_Replace(ToString([Field1]), "\d?(\d{6})", "$1")

 

Try that.

 

But I warn you that if you change your field to Numeric Type, this " 020906" will appear as "20906"

Cheers,

Asteroid

It works but is removing a zero (20906) and I want to  look like 020906 on the output

Asteroid

I need that first zero. Is very important the whole field needs to be 6 digits.

 

Thanks

Evans

Alteryx Certified Partner

@EvansM 

 

Then you need to keep the field as a String.

 

Use the Regex I provided you and keep the field as V_WString and you'll be fine.

 

Cheers,

Quasar

Hi @EvansM ,

 

A simple string of text formulas should do the trick.

 

Make sure that the field [Date] is string (I did this with a select tool, but you could also use tostring() in the formula

 

Try this formula for your [Expected Results] field, making sure that the output is a string so you don't lose leading zeros.

 

IF Length([Date])=6
THEN [Date]
ELSE Right([Date],6)
ENDIF

 

Will work for any [Date] strings over 6 characters.

 

Cheers!

Esther

@EvansM  Similar to what @EstherB47  has, I would use this in a formula tool:

Right(tostring(Date),6)

Capture.PNG

Asteroid

Thanks a lot EstherB47 it works

Alteryx Certified Partner
Alteryx Certified Partner

My favorite solution here is @EstherB47 .

 

But ... (as the shoe falls),

 

I would just use:

 

Right([Date],6)

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels