+ Reply to Thread
Results 1 to 9 of 9

Formula to break cells but also to insert different text if condition met

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    Macedonia
    MS-Off Ver
    2010
    Posts
    69

    Formula to break cells but also to insert different text if condition met

    Hello,

    A text cells range is populated with data that looks like this "123456.01:78901". The numbers can be different but there is always a semicolon.

    Sometimes there will be only one cell populated in the range with this data (for example D4) and yet in other occasions there could be more cells populated with the same type of data (for example D4:D10).

    With the formulas
    Please Login or Register  to view this content.
    and
    Please Login or Register  to view this content.
    I am copying the numbers before and after the semicolon in two different cells S1 and S2 showing them separately.

    But what I need is this:

    Scenario 1:

    If only D4 is populated with "123456.01:78901" than in S1 I will get "123456.01" and in S2 I will get "78901".

    So far so good.

    Scenario 2:

    Now if in D4:D10 I have the same text "123456.01:78901" it is perfectly fine if in S1 and S2 I have the same result as in scenario 1.

    Scenario 3 (the tricky part):

    If D4 = 123456.01:78901 but some other cell in the range D4:D10 has different value (for example "654321.01:10987"), than in both S1 and S2 I would like just the text "see below" to be displayed.

    I hope I was clear.

    Thank you so much.
    Last edited by gokica; 11-19-2014 at 06:59 AM.

  2. #2
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Formula to break cells but also to insert different text if condition met

    Hi,

    Please Login or Register  to view this content.
    Regards
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

  3. #3
    Registered User
    Join Date
    10-21-2014
    Location
    Macedonia
    MS-Off Ver
    2010
    Posts
    69

    Re: Formula to break cells but also to insert different text if condition met

    Quote Originally Posted by karedog View Post
    Hi,

    Please Login or Register  to view this content.
    Regards
    Works wonderful. Thank you soooo much.

  4. #4
    Registered User
    Join Date
    10-21-2014
    Location
    Macedonia
    MS-Off Ver
    2010
    Posts
    69

    Re: Formula to break cells but also to insert different text if condition met

    Quote Originally Posted by karedog View Post
    Hi,

    Please Login or Register  to view this content.
    Regards
    I am so sorry for this double post. But I missed one scenario. It is possible that the cell range D4:D10 will remain blank with no data. In this case how will I avoid having "#Value!" in cells S1 and S2. I would like these two cells to be blank as well.

    Thank you so much.

  5. #5
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Formula to break cells but also to insert different text if condition met

    Please Login or Register  to view this content.
    You are welcome and thanks for the reputation point.

    Regards

  6. #6
    Registered User
    Join Date
    10-21-2014
    Location
    Macedonia
    MS-Off Ver
    2010
    Posts
    69

    Re: Formula to break cells but also to insert different text if condition met

    Quote Originally Posted by karedog View Post
    Please Login or Register  to view this content.
    You are welcome and thanks for the reputation point.

    Regards
    Excel prompts that there is an error when I add IF(ISBLANK...). I wonder what is the problem. Thank you.

  7. #7
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Formula to break cells but also to insert different text if condition met

    Oh, I'm sorry, that is an array formula. Please change to the simpler one :

    Please Login or Register  to view this content.
    Regards

  8. #8
    Registered User
    Join Date
    10-21-2014
    Location
    Macedonia
    MS-Off Ver
    2010
    Posts
    69

    Re: Formula to break cells but also to insert different text if condition met

    Quote Originally Posted by karedog View Post
    Oh, I'm sorry, that is an array formula. Please change to the simpler one :

    Please Login or Register  to view this content.
    Regards
    Absolutely miraculous. Thank you so much.

    Wishing you a wonderful day.

  9. #9
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: Formula to break cells but also to insert different text if condition met

    You are welcome, and wishing you have a great day.

    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Insert line break and format text with CONCATENATE (or &)
    By AnnieMcken in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2014, 12:24 PM
  2. if condition to use the same formula with two cells and to enter text
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2012, 10:15 PM
  3. [SOLVED] Formula to insert text if text condition is met
    By koshersalt in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-24-2012, 08:44 PM
  4. Insert manual line break in formula
    By 0-0 Wai Wai ^-^ in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-11-2005, 03:15 PM
  5. Insert maunal line break in formula
    By 0-0 Wai Wai ^-^ in forum Excel General
    Replies: 2
    Last Post: 09-10-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1