Question about splitting cell/column

[khyber]

I have a column in my spreadsheet called Model#. The format of these model numbers look like this:

T Bold 5000.FX.B

The dots are basically separators. I would like to copy over these model numbers to another column but remove the last dot and whatever that comes after it. For example:

T Bold 5000.FX.B --> T Bold 5000.FX
Bold 2500.GB..B --> Bold 2500.GB.
TM 25.BO.A1.T --> TM 25.BO.A1

How can I do this using a function? There are a lot of model numbers and doing it manually is very hard.
I have been looking at the RIGHT+SEARCH function but couldn't really find a solution.
Thanks in advance.

 

[Rick Rothstein]

I have a column in my spreadsheet called Model#. The format of these model numbers look like this:

T Bold 5000.FX.B

The dots are basically separators. I would like to copy over these model numbers to another column but remove the last dot and whatever that comes after it. For example:

T Bold 5000.FX.B --> T Bold 5000.FX
Bold 2500.GB..B --> Bold 2500.GB.
TM 25.BO.A1.T --> TM 25.BO.A1

How can I do this using a function? There are a lot of model numbers and doing it manually is very hard.
I have been looking at the RIGHT+SEARCH function but couldn't really find a solution.
Thanks in advance.

If the model numbers you are modifying always only have one letter following that last dot (as your examples all show), then you can use this formula...

=LEFT(A1,LEN(A1)-2)

 

[khyber]

If the model numbers you are modifying always only have one letter following that last dot (as your examples all show), then you can use this formula...

=LEFT(A1,LEN(A1)-2)

Some don't even have a letter following the dot.

HansV from MS Community showed me this function:

=LEFT(D2,FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",""))))-1)


It works perfectly but I am having hard time understanding how it works. Maybe you can explain better?

Thanks for the help though.

 

[Rick Rothstein]

Some don't even have a letter following the dot.

HansV from MS Community showed me this function:

=LEFT(D2,FIND("@",SUBSTITUTE(D2,".","@",LEN(D2)-LEN(SUBSTITUTE(D2,".",""))))-1)

It works perfectly but I am having hard time understanding how it works. Maybe you can explain better?

Thanks for the help though.

About the two lines I highlighted in red... please read Rule #10 from this forum's rules here...

http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

and in particular click the link at the end of Message #10 to find out why what you did was a bad thing!

 

[khyber]

About the two lines I highlighted in red... please read Rule #10 from this forum's rules here...

http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

and in particular click the link at the end of Message #10 to find out why what you did was a bad thing!

My mistake. For some reason I cannot edit my post to add the link to the original post.

 

[markmzz]

Maybe this:

=IFERROR(REPLACE(A1,LOOKUP(9^9,FIND(".",A1,ROW($1:$999))),999,""),A1)

Markmzz

 

[Rick Rothstein]

My mistake. For some reason I cannot edit my post to add the link to the original post.

You only get about 10 minutes to be able to edit a message you post to this forum. Once that time has passed, the best you can do is to post a response to your original message and indicate your question was asked elsewhere and provide the link to it. Of course, for future message you post here, it would be best to provide the link in your original message. Oh, by the way, to be fair to those in the MS Community, you should post a message there indicating you asked your question in another forum and post a link there to your thread here in this forum.

 

[markmzz]

Maybe this:

=IFERROR(REPLACE(A1,LOOKUP(9^9,FIND(".",A1,ROW($1:$999))),999,""),A1)

Markmzz

Hi Khyber!

Did you try my formula?

Here is the results of my formula:

[TABLE="width: 203"]
<tbody>[TR]
[TD="class: xl65, width: 141, bgcolor: transparent"]Data
[/TD]
[TD="class: xl65, width: 129, bgcolor: transparent"]Result
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]T Bold 5000.FX.B
[/TD]
[TD="class: xl65, bgcolor: transparent"]T Bold 5000.FX
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bold 2500.GB..B
[/TD]
[TD="class: xl65, bgcolor: transparent"]Bold 2500.GB.
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T tetetetete
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T werr
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1ncnncncnc.T
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1ncnncncnc
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.tetetteBO.A1.T
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.tetetteBO.A1
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25
[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]**********************
[/TD]
[TD="class: xl66, bgcolor: transparent"]********************
[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

 

[khyber]

I have a column in my spreadsheet called Model#. The format of these model numbers look like this:

T Bold 5000.FX.B

The dots are basically separators. I would like to copy over these model numbers to another column but remove the last dot and whatever that comes after it. For example:

T Bold 5000.FX.B --> T Bold 5000.FX
Bold 2500.GB..B --> Bold 2500.GB.
TM 25.BO.A1.T --> TM 25.BO.A1

How can I do this using a function? There are a lot of model numbers and doing it manually is very hard.
I have been looking at the RIGHT+SEARCH function but couldn't really find a solution.
Thanks in advance.

FYI: This question was also asked at MS Community:
Split a cells/column - Microsoft Community

 

[khyber]

Hi Khyber!

Did you try my formula?

Here is the results of my formula:

[TABLE="width: 203"]
<tbody>[TR]
[TD="class: xl65, width: 141, bgcolor: transparent"]Data[/TD]
[TD="class: xl65, width: 129, bgcolor: transparent"]Result[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]T Bold 5000.FX.B [/TD]
[TD="class: xl65, bgcolor: transparent"]T Bold 5000.FX[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Bold 2500.GB..B [/TD]
[TD="class: xl65, bgcolor: transparent"]Bold 2500.GB.[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T [/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T tetetetete[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T werr[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1ncnncncnc.T [/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1ncnncncnc[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1.T [/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.BO.A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25.tetetteBO.A1.T [/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25.tetetteBO.A1[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]TM 25[/TD]
[TD="class: xl65, bgcolor: transparent"]TM 25[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]**********************[/TD]
[TD="class: xl66, bgcolor: transparent"]********************[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz

Yes, this worked too. Thank you everyone for the help.