Using INDEX function

[Nagaraju_KCS]

Hi All,

Phone:

Load * inline[

Phone

98859843841];

i want to return 988-584-3841 this format

Phone:

LOAD

  Phone,

  Left(Phone, Index(Phone, '-')-1) as Phone1,

  Mid(Phone, Index(Phone, '-')+1, 3) as Phone2,

  Right(Phone, Len(Phone)-Index(Phone, '-', 2)) as Phone3

Resident Phone;

[jagan]

Hi,

Try like this

=Left('98859843841', 3) &'-'&Mid('98859843841', 4,3) &'-'&Right('98859843841', 4)

will gives u this format 988-584-3841

If you want 98859843841 from 988-584-3841

Then use

SubField()

SubField('988-584-3841', '-') will gives you 988

SubField('988-584-3841', '-', 2) will gives you 584

SubField('988-584-3841', '-', 3) will gives you 3841

Regards,

Jagan.

[#3]

Hola Raju,

Do you want 9885843841 to be 988-584-3841? Or, do you want  988-584-3841 to be 9885843841?

Why don't you concatenate the string Phone1, Phone2 and Phone3?

Regards, Karl

[tresesco]

Try:

Phone:

LOAD

  Phone,

  Left(Phone, 3) as Phone1,

  Mid(Phone, 4,3) as Phone2,

  Mid(Phone,8) as Phone3,

Left(Phone, 3) &'-'& Mid(Phone, 4,3)&'-'& Mid(Phone, 😎 as PhoneWithSeparator

Resident Phone;

[#5]

hi

try this

there are many ways, but try one,

=Left('98859843841',3) &'-' &left(mid('98859843841',4,4),1)&right(mid('98859843841',4,4),2) &'-'& Right('98859843841',4)

or try this

=Left(PHONE,3) &'-' &left(mid(PHONE,4,4),1)&right(mid(PHONE,4,4),2) &'-'& Right(PHONE,4)

[#6]

This worked...

PhoneNumber:

LOAD * INLINE [
Phone
98859843841
];

Try this..
Phone:
LOAD
Phone,
left(Phone,3)&'-'& mid(Phone,4,3)&'-'&Right(Phone,4) as new
Resident PhoneNumber;

[sunilkumarqv]

try this in your script

LOAD Left(Phone, 3) &'-'&Mid(Phone, 4,3) &'-'&Right(Phone, 4) as Phone;

Load * inline

[

Phone

98859843841];