# Excel - IF Function Problem - Expert Solution

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
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
Excelchat Expert 11/10/2018 - 03:06
If A2 is "", then it will automatically be empty, right?
User 11/10/2018 - 03:06
Excelchat Expert 11/10/2018 - 03:07
User 11/10/2018 - 03:08
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
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.