We help IT Professionals succeed at work.

separate text from column

Julio Jose
Julio Jose asked
on
I have one column A many row data, how to use formula to get the output in column B and C?

Image-767.png
Comment
Watch Question

Naresh PatelFinancial Adviser

Commented:
instead of formula better to use Text To Column Function of excel is good.

Tab - Data - Data Tools - Text To column

Thanks

Author

Commented:
I need to get the selected string after "remote from" and "Username"
Naresh PatelFinancial Adviser

Commented:
Assuming Your Data is in Cell A1

Than Formula is
=MID(A1,FIND(":",A1)+2,2)

Open in new window

=RIGHT(A1,LEN(A1)-SEARCH(":",A1,FIND(":",A1)+1))

Open in new window


Thanks
Naresh PatelFinancial Adviser

Commented:
Assuming you want only two characters From remote from.

=MID(A1,FIND(":",A1)+2,2)

Open in new window

=RIGHT(A1,LEN(A1)-(FIND(":",A1,FIND(":",A1)+1)+1))

Open in new window

See Attached
Text.xlsx
Excel VBA Developer
Top Expert 2014
Commented:
Presuming that the markers "Userform : " and "Remote From : " are constant (that is, they both exist in every source line and both also include the space-colon-space after each), the following two formulas can be used to extract the Username and the Remote From (IP address).  This will work regardless of the length or composition (i.e., any punctuation) in the Username.

Assuming first data is in cell A2...
Username
B2: =MID(A2,FIND("Username",A2)+11,FIND("Remote From",A2)-FIND("Username",A2)-12)

Remote From
C2: =MID(A2,FIND("Remote From : ",A2)+14,15)

See the attached workbook for an example.

Regards,
Glenn
EE_ParseString.xlsx

Author

Commented:
Thanks
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
You're welcome.  I'm glad I could help.