Function to format cells???

  • Thread starter Thread starter 43fan
  • Start date Start date
4

43fan

I have a series of data that gets pulled into a worksheet(it's pulled into a
"data" sheet first from a text file, then creates a "report" from the data
in the worksheet). In most cases, the one set of data will have a product
number and a item number separated by a dash. I have a function that takes
only the portion of the number after the dash, puts a comma after it, then
the next item, comma, etc. This works great for all but one customer, who
doesn't have dashes! Of course, right!? ;) Anyway, what I end up doing is
formatting the cell then to allow wrapping, and then manually adjusting the
width of the cell so it wraps right at the comma.

Is there any way to do this automatically? IOW, when it doesn't find a
dash, set it to wrap text, at the comma(actually, right after the comma)?

Thanks!
Shawn
 
=IF(C2=1,"abcdefg","abcd<alt-enter-symbol>efg");
this will wrap the text "abcdefg" after the "d" if cell C1 is not equal to
1.

I copied the alt-enter special character from a line of text and pasted it
into the formula above at the place marked <alt-enter-symbol> but you can
just hit alt-enter at that spot in the formula.
NB. The cell containing the formula needs to be formatted as wrapped text,
otherwise you will see a square.
 
Landxlii,

This is close, but I don't have the experience to fix it. I'm wondering if
this would be something that'd be better to "program" as a function?

The problem with this is that for the report, I'm pulling information from
another worksheet in the workbook. Right now to get the numbers after the
dash and put the comma's in where they're supposed to be, I use this:

=rightofdash('Veh 1 Data'!$J1) & "," & rightofdash('Veh 1 Data'!$J2) & "," &
rightofdash('Veh 1 Data'!$J3) & "," & rightofdash('Veh 1 Data'!$J4)

with "rightofdash" being a function that finds the dash, then pulls just the
characters to the right of it. As you can see, I then concatenate them
together to put the commas in.

What I need to do is, with data that doesn't have the dash, is go ahead and
concatenate them with the commas, but then have the cell automatically wrap
and adjust to size, so the commas are in a line at the right.

Something like this:
Data before formatting: abcd,efgh,ijkl

Data after formatting:
abcd,
efgh,
ijkl

Can you help?

Thanks!!
Shawn
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top