Extract first 3 strings in 3 different cells

Hi,

Would like to have a formula to separate first 3 strings by " :" in 3 different cells

Hostname1.gmail.com :C4_F6_AM_REB_DF3_MCS_ESA_DB :1 :Backup1
Cell1 Hostname1
Cell2 Name
Cell2 Number

Hostname2 :C4_F6_AM_AA_DF3_MCS_ESA_DB :2 :Backup2
Cell1 Hostname2
Cell2 Name
Cell3 Number

Cell1 (I just need to retrieve the hostname but it has 2 different separators which is " :" and "." Please advise to combine below formula.
=LEFT(T3,FIND(" ",SUBSTITUTE(T3,":"," ",2))-1)
=LEFT(T3,FIND(" ",SUBSTITUTE(T3,"."," ",2))-1)

I also need to separate the string for 2nd and 3rd string as well.

Please give your advise. Thank you.

result?

Formula:

Please Login or Register  to view this content.

if yes, use Text to columns twice

Try

in B1 and copy across

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",250)),(COLUMNS($A:A)-1)*250+1,250))

Hi John,
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",250)),(COLUMNS($A:A)-1)*250+1,250)) worked well.

But in Cell 1, I have 2 different string of hostname which is as follows. Would like to retrieve the hostname only. Please advise.

Hostname1.gmail.com : (dot)
Hostname2 : (Colon)

Did you try Text to columns?
Faster, easier, not copied/duplicated data, everything what are you doing is on A column only
but it's up to you

Yes I know how to use the Text columns but I'm just saving time to prepare report every morning.

If I understand well source data will stay as well result in the same worksheet, and every morning you want to use formula to get new result?

Do you mean ?

Hostname1.gmail.com . Hostname2 : C4_F6_AM_REB_DF3_MCS_ESA_DB :1 :Backup1

Post sample fle

To attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

John, look at #3, it should looks like there (probably)

in B1

=LEFT(A1,FIND(".",A1)-1)

in C1

=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",250)),(COLUMNS($A:B)-1)*250+1,250))

Copy across to D

Sorry for the confusion

The purpose of doing this is that I'm designing a dashboard. To save time in preparing the report, users will just need to copy and past the raw data.
On the right side of the raw data, formulas are set to populate the trending to find the gaps to further discuss on reoccurance and failures.

Try this ...

In B3:

=MID(SUBSTITUTE($A3,":",REPT(" ",255)),255,255)

In C3:

=MID(SUBSTITUTE($A3,":",REPT(" ",255)),255,255)

In D3:

=LOOKUP(10^10,--LEFT(TRIM(MID(SUBSTITUTE($A3,":",REPT(" ",255)),510,255)),{1,2,3,4,5,6,7,8,9}))

Hi Phuocam

In B3:
I have changed from MID to Left but it's not working for hostname3.group.abc.com. I just need the hostname3 in the same cell.
I also found out that it also created so many spaces.

=LEFT(SUBSTITUTE($A3,":",REPT(" ",255)),255,255)


In C3:
This formula works but it created so many spaces when double click the column.
=MID(SUBSTITUTE($A3,":",REPT(" ",255)),255,255)

Attached is the results.

JohnTopley's formula =TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",250)),(COLUMNS($A:B)-1)*250+1,250)) worked for Cell C3.

Only struggling for Cell B3 where I need to populate hostname only.
Please advise.

Sorry, try again:

In B3:

=LEFT(A3,FIND(":",SUBSTITUTE($A3,".",":"))-1)

In C3:

=TRIM(MID(SUBSTITUTE($A3,":",REPT(" ",255)),255,255))

In B3

=LEFT(A3,FIND(":",SUBSTITUTE($A3,".",":"))-1)

in C3

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A3,"(",":"),":",REPT(" ",250)),(COLUMNS($A:B)-1)*250+1,250))

drag to D3

See E, F, G in attached

Hi Phuocam and John,

Wow!! I didn't know by simply adding another separator within the formula.
Appreciate for the efforts, both the formulas worked.

Thank you.