Question Locked

Sort in Excel and replacing chars

0 helpful
2 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have 18000+ rows in excel, each row has a different value on it. For Example:

PTH03020WAZ
PTH03030WAZ
PTH03030WAD
PTH03050WAZ
PTH03050WAD
PTH03050WAH.
PTH03060WAZ
PTH03060WAD
PTH03060WAH
PTH03060WAH.

Now if the first 8 chars are the same, I want to remove the remaining chars and replace with "(Space) Series"

Can anyone help with this?!

Thank you in advance!

Ross

2 replies

by locolobo

Are you wanting a list like this:

PTH03030 WAD
PTH03050 WAZ
WAD
WAH
PTH03060 WAZ
etc.?

You can use the left and right functions to separate the characters into different columns. For instance:

=left(a1,8) then =right(a1,len(a1)-8)

You can even check to see if the first 8 characters match a given value:

=if(left(a1,8)="PTH03060",right(a1,len(a1)-8),"")

Is that kinda like what you want?

This discussion is closed to new replies.