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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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
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
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,

Pulsar

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

Highlighted
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