Question description:
This user has given permission to use the problem statement for this
blog.
Hello, I need help with IF and SUBSTITUTE formula in excel
Solved by A. A. in 43 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
11/10/2018 - 02:43
Yes
Excelchat Expert
11/10/2018 - 02:43
Hello, I understand that you need help with IF and SUBSTITUTE.
User
11/10/2018 - 02:43
Yes
Excelchat Expert
11/10/2018 - 02:43
Can you tell me more?
User
11/10/2018 - 02:44
I have a formula and I want you to evaluate
User
11/10/2018 - 02:44
Is it oke
User
11/10/2018 - 02:44
*ok
Excelchat Expert
11/10/2018 - 02:45
Sure let me have it please.
User
11/10/2018 - 02:45
=IFERROR (IF(A2<>""(SUBSTITUTE(SUBSTITUTE(IF(LEFT(E2,1)=".",RIGHT(E2,LEN(E2)-1)),".","'"),".RT","'R")&"-T",A2&"-US"),""))
Excelchat Expert
11/10/2018 - 02:45
As long as its about the IF and SUBSTITUTE you mentioned in the description we can work on it.
User
11/10/2018 - 02:45
Yes it is
Excelchat Expert
11/10/2018 - 02:45
Alright, this would be a lot easier with data. Are you able to provide the data that it works with?
User
11/10/2018 - 02:47
Suppose if the text is .ABC.B it should return as ABC-US
User
11/10/2018 - 02:47
If it is ABC.A , it should return as ABC'A
User
11/10/2018 - 02:48
The first one is incorrect. it should .ABC.B , it should return as ABC'B
User
11/10/2018 - 02:48
*if .ABC.B
Excelchat Expert
11/10/2018 - 02:49
Can you give me actual examples so I can build a pattern out of them? I'm asking because using your example then the pattern I can see is that the . after the letter C would be converted to ' and the first . will be removed.
Excelchat Expert
11/10/2018 - 02:49
But I'm guessing that ABC is a placeholder so I can't really use those letters to build a pattern.
User
11/10/2018 - 02:51
It is the actual example
Excelchat Expert
11/10/2018 - 02:52
Will all of your data have ABC?
User
11/10/2018 - 02:52
no
Excelchat Expert
11/10/2018 - 02:52
Okay, will all of them have . after the 3rd letter?
User
11/10/2018 - 02:52
yes
Excelchat Expert
11/10/2018 - 02:53
Will all of them only have a single letter after the .
User
11/10/2018 - 02:53
some will have . in front
User
11/10/2018 - 02:54
some will have WT or RT after the . after the third letter
Excelchat Expert
11/10/2018 - 02:54
Alright, I can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
11/10/2018 - 02:54
Yes sure
Excelchat Expert
11/10/2018 - 02:54
Okay, I will really have to be honest with you. This is going to be extremely difficult because I don't have all the information that I need in order to complete the formula.
User
11/10/2018 - 02:55
Oh I see
Excelchat Expert
11/10/2018 - 02:55
The formula I'll be able to provide you will only work based on the example you gave me.
User
11/10/2018 - 02:55
Yes that's ok
Excelchat Expert
11/10/2018 - 02:55
But if there are any variations, the formula is not guaranteed to work
Excelchat Expert
11/10/2018 - 02:55
So before I begin, are you sure you don't want to provide more data samples?
User
11/10/2018 - 02:56
If there is no . in the front, it should add -T
Excelchat Expert
11/10/2018 - 02:56
I mean, if your data does not have any personal information, you could upload it so I can directly work on it.
User
11/10/2018 - 02:56
If not . in the front, it should add -US in the end
Excelchat Expert
11/10/2018 - 02:56
There, those are the information that I need, otherwise there'll be no end to this because I'll complete this and then you will remember that you need something else.
Excelchat Expert
11/10/2018 - 02:57
So before I begin working on it, please ensure that everything you need is mentioned and if possible upload your data.
User
11/10/2018 - 02:57
Yes this is it.
Excelchat Expert
11/10/2018 - 02:57
Okay, let me summarize what we've discussed so far.
User
11/10/2018 - 02:57
Sure
Excelchat Expert
11/10/2018 - 02:58
About the -T and -US
Excelchat Expert
11/10/2018 - 02:58
Can you clarify please? The info you gave above is if there is no . in front for both.
User
11/10/2018 - 02:59
If there is a . in the front, it should add -T as suffix
User
11/10/2018 - 02:59
If there is no . in the front, it should add -US as suffix
Excelchat Expert
11/10/2018 - 02:59
Okay, thank you.
Excelchat Expert
11/10/2018 - 03:00
Here's what I have so far and please extend the session.
Excelchat Expert
11/10/2018 - 03:00
If there is a . in the front, it should add -T as suffix
If no . in the front, it should add -US in the end
Remove first .
substitute second . with '
User
11/10/2018 - 03:00
Ok
User
11/10/2018 - 03:00
Yes
Excelchat Expert
11/10/2018 - 03:00
Alright, I'll work on this locally on my Excel and will be back in 5 to 10 minutes.
User
11/10/2018 - 03:01
That's fine
User
11/10/2018 - 03:01
Thank you!
Excelchat Expert
11/10/2018 - 03:04
Making progress.
User
11/10/2018 - 03:06
No problem
User
11/10/2018 - 03:06
Take your time
Excelchat Expert
11/10/2018 - 03:06
If A2 is "", then it will automatically be empty, right?
User
11/10/2018 - 03:06
yes please
Excelchat Expert
11/10/2018 - 03:07
Please see this file.
[Uploaded an Excel file]
User
11/10/2018 - 03:08
I am not able to download the file
User
11/10/2018 - 03:09
It is going to the site
Excelchat Expert
11/10/2018 - 03:09
Just try this formula in your file then:
Excelchat Expert
11/10/2018 - 03:09
=IF(A2="","",IF(LEN(E2)-LEN(SUBSTITUTE(E2,".",""))=1,SUBSTITUTE(E2,".","'")&"-US",RIGHT(SUBSTITUTE(E2,".","'"),LEN(E2)-1)&"-T"))
User
11/10/2018 - 03:09
Ok. Let me check
User
11/10/2018 - 03:10
-US is not showing if there is no . in the front
User
11/10/2018 - 03:11
otherwise it is good
Excelchat Expert
11/10/2018 - 03:11
It does show on my end.
Excelchat Expert
11/10/2018 - 03:11
Unless there's something unique about your actual data.
User
11/10/2018 - 03:11
Just type ABC and it should show ABC-US
Excelchat Expert
11/10/2018 - 03:12
I thought there's always . in your data. This is what I'm referring to earlier that it would be extremely difficult to build a formula for this without seeing your actual data.
User
11/10/2018 - 03:13
No there is no. in the front or in the end
Excelchat Expert
11/10/2018 - 03:13
I'm sure there are other unique circumstances that we haven't discussed that will come up later.
User
11/10/2018 - 03:13
or in between
Excelchat Expert
11/10/2018 - 03:13
I understand but please just send your data so I can work on it. :(
User
11/10/2018 - 03:13
can I type it in the spread sheet
Excelchat Expert
11/10/2018 - 03:14
You can just make sure there data you will be typing is accurate otherwise the formula may fail again.
User
11/10/2018 - 03:14
Yes I am sure
Excelchat Expert
11/10/2018 - 03:14
Type a few examples and make sure all variations are included.
User
11/10/2018 - 03:14
Sure
Excelchat Expert
11/10/2018 - 03:14
Like without . or with . or with 2 .
Excelchat Expert
11/10/2018 - 03:15
Click Edit Document and type. we have less than 27 minutes remaining.
User
11/10/2018 - 03:16
I am not able to Edit
Excelchat Expert
11/10/2018 - 03:17
Are you not able to upload your file using the clip icon next to this chat?
User
11/10/2018 - 03:18
I am not supposed to attach any document because it is my work computer
Excelchat Expert
11/10/2018 - 03:19
Alright, then I've explained how important it is that I have access to the actual data in order to build an accurate formula but since we can't reach that we'll have to just go the trial and error approach.
Excelchat Expert
11/10/2018 - 03:19
Please try this formula:
Excelchat Expert
11/10/2018 - 03:19
=IF(LEFT(E2,1)=".",RIGHT(SUBSTITUTE(E2,".","'"),LEN(E2)-1)&"-T",SUBSTITUTE(E2,".","'")&"-US")
Excelchat Expert
11/10/2018 - 03:20
Here's the version that will check for column A first:
Excelchat Expert
11/10/2018 - 03:20
=IF(A2="","",IF(LEFT(E2,1)=".",RIGHT(SUBSTITUTE(E2,".","'"),LEN(E2)-1)&"-T",SUBSTITUTE(E2,".","'")&"-US"))
User
11/10/2018 - 03:22
I am checking ..
User
11/10/2018 - 03:25
Awesome
User
11/10/2018 - 03:25
It is working
User
11/10/2018 - 03:25
Thank you !
Excelchat Expert
11/10/2018 - 03:25
Please check for all variations so we can be sure that it's the formula that you need. I apologize if it took this long as well.
User
11/10/2018 - 03:25
No issues
User
11/10/2018 - 03:25
I understand
User
11/10/2018 - 03:25
Thanks once again
Excelchat Expert
11/10/2018 - 03:25
Thank you. I'm glad to have helped!
User
11/10/2018 - 03:25
I will subscribe to your site
Excelchat Expert
11/10/2018 - 03:26
Would there be anything else that I can help you with regards to the original question?
User
11/10/2018 - 03:26
Impressesed :)
Excelchat Expert
11/10/2018 - 03:26
That's great news!
User
11/10/2018 - 03:26
No, thank you !
Excelchat Expert
11/10/2018 - 03:26
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
11/10/2018 - 03:26
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
User
11/10/2018 - 03:26
Sure
Excelchat Expert
11/10/2018 - 03:26
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
User
11/10/2018 - 03:26
Sure , I will do right away
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
