Extract the 6 digit value from the string

[rajuganapathy]

Hi All,

Hope you are doing well... let's get started my question here. i have a excel file which contains 1000 rows based on the SNOW ticket was raised ( three columns 1. subject 2.created date 3. Tkt problem).

the challenge is here the third column Please refer the screen shot it's showing warp text unable to split that and i need the split "NSI124" and "MJA268" these 6 digit numbers only.

is this possible to split the data. Thank you.

Thanks/Raju

[Olly]

Will the field names ("User id", "Site id" and "Mirror id" always be the same, and always be present in the text? Is the colon always present between the field name and the value?

One row of sample data is not much to work with...

[Marc L]

Hi !

is this possible to split the data.

Use MATCH worksheet function or Range.Find VBA method to locate desired cells
then extract the text via Mid, Replace or Split VBA functions, to see in VBA inner help …

[rajuganapathy]

Hi Olly,

yes it should be the same only...

Thanks/Raju

[AlKey]

It shouldn't be to much trouble to extract IDs from the string
Enter formula in B2 and drag formula across.

Please Login or Register  to view this content.

v	A	B	C	D
1
2	USER ID : NSI124 SITE ID : INDI02 MIRROR ID : MJA268	NSI124	INDI02	MJA268

[rajuganapathy]

Hi Alkey,

Thanks for your reply, but it will not work.Cause i have wrap text format in excel copied from somewhere while you expand/ Press F2 the above said info was appear. so am attaching the file for your perusal. Thank you.

[jindon]

Please Login or Register  to view this content.

[rajuganapathy]

Genius Jindon, indeed working perfect but as a vba learner i didn't understand the code how it's working would you please help me get an knowledge on coding.....

Regards/Raju

[jindon]

Used Regular Expression. (google for the details)

Find matching pattern that is

UID or User ID
Mirror ID

followed by a colon or hyphen and the rest up to end of the line.

This is according to your uploaded data.